On Sun, Nov 30, 2014 at 12:51 PM, Tom Lane <t...@sss.pgh.pa.us> wrote: > Bottom line, given all the restrictions on whether the optimization can > happen, I have very little enthusiasm for the whole idea. I do not think > the benefit will be big enough to justify the amount of mess this will > introduce.
This optimization applies to a tremendous number of real-world cases, and we really need to have it. This was a huge problem for me in my previous life as a web developer. The previous work that we did to remove LEFT JOINs was an enormous help, but it's not enough; we need a way to remove INNER JOINs as well. I thought that David's original approach of doing this in the planner was a good one. That fell down because of the possibility that apparently-valid referential integrity constraints might not be valid at execution time if the triggers were deferred. But frankly, that seems like an awfully nitpicky thing for this to fall down on. Lots of web applications are going to issue only SELECT statements that run as as single-statement transactions, and so that issue, so troubling in theory, will never occur in practice. That doesn't mean that we don't need to account for it somehow to make the code safe, but any argument that it abridges the use case significantly is, in my opinion, not credible. Anyway, David was undeterred by the rejection of that initial approach and rearranged everything, based on suggestions from Andres and later Simon, into the form it's reached now. Kudos to him for his persistance. But your point that we might have chosen a whole different plan if it had known that this join was cheaper is a good one. However, that takes us right back to square one, which is to do this at plan time. I happen to think that's probably better anyway, but I fear we're just going around in circles here. We can either do it at plan time and find some way of handling the fact that there might be deferred triggers that haven't fired yet; or we can do it at execution time and live with the fact that we might have chosen a plan that is not optimal, though still better than executing a completely-unnecessary join. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
