Re: Further thoughts about warning for costly FK checks

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