On Tue, 2008-09-02 at 12:05 +0100, Gregory Stark wrote: > I wonder if it would be more worthwhile to remove them and have a subsequent > phase where we look for possible joins to *add*. So even if the user writes > "select * from invoices where customer_id=?" the planner might be able to > discover that it can find those records quicker by scanning customer, finding > the matching <company_id,customer_id> and then using an index to look them up > in invoices.
This seems a less useful idea now just simply because it is such a special case. We would need to have a case where we have a table A that does not have an index on a specific column, yet table B does have an index on the specific column. But also when A references B as a foreign key and where the column is a subset of the columns of the primary key of B. That means only queries like select ... from a where a.col2 = x; can be transformed into select ... from a join b on (foreign key cols) where a.col2 = x; and then because a.col2 is a subset of foreign key columns we can infer that b.col2 = x. So the pre-conditions for this to be useful are: * constraint on subset of a FK * subset of FK is indexed on B * subset of FK is not indexed on A Which doesn't seem that likely to occur. Thanks both to Heikki and Greg for good, fast input on this patch. Nothing more needed now while I rework patch. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- Sent via pgsql-patches mailing list (pgsql-patches@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-patches
