On Mon, Jun 2, 2014 at 11:42 AM, Tom Lane <t...@sss.pgh.pa.us> wrote: > Stephen Frost <sfr...@snowman.net> writes: >> * Tom Lane (t...@sss.pgh.pa.us) wrote: >>> TBH I think that trying to do anything at all for inner joins is probably >>> a bad idea. The cases where the optimization could succeed are so narrow >>> that it's unlikely to be worth adding cycles to every query to check. > >> I agree that we don't want to add too many cycles to trivial queries but >> I don't think it's at all fair to say that FK-check joins are a narrow >> use-case and avoiding that join could be a very nice win. > > [ thinks for a bit... ] OK, I'd been thinking that to avoid a join the > otherwise-unreferenced table would have to have a join column that is both > unique and the referencing side of an FK to the other table's join column. > But after consuming more caffeine I see I got that backwards and it would > need to be the *referenced* side of the FK, which is indeed a whole lot > more plausible case.
Back when I did web development, this came up for me all the time. I'd create a fact table with lots of id columns referencing dimension tables, and then make a view over it that joined to all of those tables so that it was easy, when reporting, to select whatever bits of information were needed. But the problem was that if the report didn't need all the columns, it still had to pay the cost of computing them, which eventually got to be problematic. That was what inspired me to develop the patch for LEFT JOIN removal, but to really solve the problems I had back then, removing INNER joins as well would have been essential. So, while I do agree that we have to keep the planning cost under control, I'm quite positive about the general concept. I think a lot of users will benefit. -- 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
