Re: Further thoughts about warning for costly FK checks

OK with me.

---------------------------------------------------------------------------

Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > I was thinking of a GUC variable called PERFORMANCE_HINTS, which would
> > throw a message if a lookup from the primary to the foreign key didn't
> > have an index.
> 
> I like the pg_advisor idea a lot better.
> 
> In the first place, a lot of these sorts of checks don't have any clean
> place to insert as a test made in-passing in regular operation.  I can't
> think of a reasonable place to do the above, for example --- the only
> way to do it at all would be to have the RI trigger code look at the
> plan it gets back to see if it's an indexscan, which is very nonmodular,
> and besides which the RI trigger couldn't really tell *why* the plan
> wasn't an indexscan; it might not be for lack of an available index.
> 
> In the second place, you don't really want notices about bad schema
> design popping out during regular operation --- they are at best noise
> from the point of view of the applications using the database.  What you
> want is something you can point at an existing database and ask for
> advice.
> 
> In the third place, if we try to solve the problem by embedding checks
> here and there in the backend, we'll limit ourselves to checks that can
> be made with minimal impact on backend performance and complexity.  And
> we'll be limiting the number of people who can contribute, because
> writing backend code is hard.  An external tool would be a lot more
> approachable IMHO.  The original suggestion for pg_advisor mentioned
> pluggable tests, which seems like the right kind of approach to me.
> 
> BTW, something that just occurred to me now: EXPLAIN is currently really
> designed only for SELECTs.  It would make sense to upgrade it for
> INSERT/UPDATE/DELETE to list the triggers that will get fired.  While
> we'd have to treat user triggers as black boxes, I think it would also
> be possible to "look inside" RI triggers and display the plans of the
> queries that will get invoked.  Not sure about the long-term usefulness
> of that, because Stephan keeps threatening to rewrite the RI
> implementation to not use normal queries ... but if it can be done
> without too much pain it'd be worth doing.
> 
>             regards, tom lane
> 

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
359-1001+  If your life is a hard drive,     |  13 Roberts Road +  Christ can be your backup.        |  Newtown Square,
Pennsylvania19073