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
featureidin (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)
wherefeatureid 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=#