Re: Further thoughts about warning for costly FK checks

OK with me.

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

Tom Lane wrote:
> Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)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(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073