On Mon, Jun 2, 2014 at 11:42 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Stephen Frost <sfrost@snowman.net> writes:
>> * Tom Lane (tgl@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
