Re: Missed index opportunity for outer join?

On Mon, 5 Dec 2005, Tom Lane wrote:
>
> Hm, I can't duplicate this in a simple test...
> Can you try updating to 8.1 branch tip ...
> Actually, a quick and dirty thing would be to try my would-be test case
> below, and see if you get a seqscan on your copy.

With your simple test-case I did not get the seqscan on 8.1.0.
Output shown below that looks just like yours.

I'll try upgrading a devel machine too - but will only be
able to try on smalller test databases in the near term.

> (Note to self: it is a bit odd that fac_id=261 is pushed down to become
> an indexqual in one case but not the other ...)

I speculate that the seq_scan wasn't really the slow part
compared to not using using both parts of the index in the
second part of the plan. The table point_features is tens of
thousands of rows, while the table facets is tens of millions.

Thanks,
Ron

===============================================================================
=== Output of Tom's test case showing the same results he got.
===============================================================================

greenie /home/pg2> createdb foo
CREATE DATABASE
greenie /home/pg2> psql foo
[...]
foo=# create table point_features(entity_id int, featureid int);
CREATE TABLE
foo=# create index point_features__featureid on point_features(featureid);
CREATE INDEX
foo=# create table facets(entity_id int, fac_id int);
CREATE TABLE
foo=# create index "fac_val(entity_id,fac_id)" on facets(entity_id,fac_id);
CREATE INDEX
foo=# set enable_hashjoin TO 0;
SET
foo=# set enable_mergejoin TO 0;
SET
foo=# explain select * from point_features upf join facets b on (b.entity_id = upf.entity_id and b.fac_id=261) where featureid in (120);
QUERY PLAN
--------------------------------------------------------------------------------------------------
Nested Loop (cost=1.03..49.15 rows=1 width=16)
-> Bitmap Heap Scan on point_features upf (cost=1.03..10.27 rows=10 width=8)
Recheck Cond: (featureid = 120)
-> Bitmap Index Scan on point_features__featureid (cost=0.00..1.03 rows=10 width=0)
Index Cond: (featureid = 120)
-> Index Scan using "fac_val(entity_id,fac_id)" on facets b (cost=0.00..3.88 rows=1 width=8)
Index Cond: ((b.entity_id = "outer".entity_id) AND (b.fac_id = 261))
(7 rows)

foo=# explain select * from point_features upf left join facets b on (b.entity_id = upf.entity_id and b.fac_id=261) where featureid in (120);
QUERY PLAN
-------------------------------------------------------------------------------------------------
Nested Loop Left Join (cost=2.07..114.25 rows=10 width=16)
-> Bitmap Heap Scan on point_features upf (cost=1.03..10.27 rows=10 width=8)
Recheck Cond: (featureid = 120)
-> Bitmap Index Scan on point_features__featureid (cost=0.00..1.03 rows=10 width=0)
Index Cond: (featureid = 120)
-> Bitmap Heap Scan on facets b (cost=1.03..10.27 rows=10 width=8)
Recheck Cond: (b.entity_id = "outer".entity_id)
Filter: (fac_id = 261)
-> Bitmap Index Scan on "fac_val(entity_id,fac_id)" (cost=0.00..1.03 rows=10 width=0)
Index Cond: (b.entity_id = "outer".entity_id)
(10 rows)

foo=#