Re: PATCH: use foreign keys to improve join estimates v1

Hi,

On 03/14/2016 02:12 PM, David Steele wrote:
> Hi Thomas,
...
> I don't think it would be clear to any reviewer which patch to apply
> even if they were working.  I'm marking this "waiting for author".

Yeah. Rebasing the patches to current master was simple enough (there
was just a simple #include conflict), but figuring out which of the
patches is review-worthy was definitely difficult.

I do believe David's last patch is the best step forward, so I've
rebased it, and made some basic aesthetic fixes (adding or rewording
comments on a few places, etc.)

The one important code change is that I've removed the piece of code
from find_best_foreign_key_quals that tried to be a bit too smart about
equivalence classes.

My understanding is that it tried to handle cases like this example:

     CREATE TABLE f (id1 INT, id2 INT, PRIMARY KEY (id1, id2));

     CREATE TABLE d1 (id1 INT, id2 INT, FOREIGN KEY (id1, id2)
                                        REFERENCES f(id1, id2));

     CREATE TABLE d2 (id1 INT, id2 INT, FOREIGN KEY (id1, id2)
                                        REFERENCES f(id1, id2));

     SELECT * FROM f JOIN d1 ON (f.id1 = d1.id1 AND f.id2 = d1.id2)
                     JOIN d2 ON (f.id1 = d2.id1 AND f.id2 = d2.id2);

But it did so by also deriving foreign keys between d1 and d2, which I
believe is wrong as there really is no foreign key, and thus no
guarantee of existence of a matching row.

FWIW as I explained in a message from 24/2/2015, while this is
definitely an issue worth fixing, I believe it needs to be done in some
other way, not by foreign keys.

Attached is v3 of the patch, and also three SQL scripts demonstrating
the impact of the patch on simple examples.


regards

--
Tomas Vondra                  http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services