Re: Further thoughts about warning for costly FK checks
От | Tom Lane |
---|---|
Тема | Re: Further thoughts about warning for costly FK checks |
Дата | |
Msg-id | 26263.1079544995@sss.pgh.pa.us обсуждение исходный текст |
Ответ на | Re: Further thoughts about warning for costly FK checks (Bruce Momjian <pgman@candle.pha.pa.us>) |
Ответы |
Re: Further thoughts about warning for costly FK checks
(Bruce Momjian <pgman@candle.pha.pa.us>)
Re: Further thoughts about warning for costly FK checks (Greg Stark <gsstark@mit.edu>) Re: Further thoughts about warning for costly FK checks (Richard Huxton <dev@archonet.com>) Re: Further thoughts about warning for costly FK checks (Fabien COELHO <coelho@cri.ensmp.fr>) |
Список | pgsql-hackers |
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
В списке pgsql-hackers по дате отправления:
Следующее
От: Fabien COELHOДата:
Сообщение: Re: Further thoughts about warning for costly FK checks