Simon Riggs wrote:
On Tue, 2008-09-02 at 13:41 +0300, Heikki Linnakangas wrote:
Simon Riggs wrote:
On Tue, 2008-09-02 at 13:20 +0300, Heikki Linnakangas wrote:
Simon Riggs wrote:
It turns out that a join like this
select a.col2
from a left outer join b on a.col1 = b.col1
where b.col2 = 1;
can be cheaper if we don't remove the join, when there is an index on
a.col1 and b.col2, because the presence of b allows the values returned
from b to be used for an index scan on a.
Umm, you *can't* remove that join.
Yes, you can. The presence or absence of rows in b is not important to
the result of the query because of the "left outer join".
I spent nearly a whole day going down that deadend also.
Oh. How does the query look like after removing the join, then?
Same answer, just slower. Removing the join makes the access to a into a
SeqScan, whereas it was a two-table index plan when both tables present.
The two table plan is added by the immediately preceding call add_... -
i.e. that plan is only added during join time not during planning of
base relations.
I mean, can you how me an SQL query of what's left after removing the
join? Certainly just removing the join and the WHERE clause doesn't give
the same answer. Or is it something that can't be expressed with SQL?
What's the filter in the SeqScan?
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
--
Sent via pgsql-patches mailing list (pgsql-patches@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-patches
