Re: Missed index opportunity for outer join? - Mailing list pgsql-performance

rm_pg@cheapcomplexdevices.com writes:
> In both cases, the outermost thing is a nested loop. The
> top subplan gets all "point features" whre featureid=120.
> The outer join did not use an index for this.
> The non-outer join did use an index for this.

Hm, I can't duplicate this in a simple test (see below).  There were
some changes in this area between 8.1.0 and branch tip, but a quick
look at the CVS logs doesn't suggest that any of them would be related
(AFAICS the intentions of the patches were to change behavior only for
OR clauses, and you haven't got any here).

Can you try updating to 8.1 branch tip and see if the problem goes away?
Or if not, generate a self-contained test case that shows the problem
starting from an empty database?

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.

            regards, tom lane

regression=# create table point_features(entity_id int, featureid int);
CREATE TABLE
regression=# create index  point_features__featureid on point_features(featureid);
CREATE INDEX
regression=# create table  facets(entity_id int,  fac_id int);
CREATE TABLE
regression=# create index "fac_val(entity_id,fac_id)" on facets(entity_id,fac_id);
CREATE INDEX
regression=# set enable_hashjoin TO 0;
SET
regression=# set enable_mergejoin TO 0;
SET
regression=# explain select *  from point_features upf join facets b on (b.entity_id = upf.entity_id and b.fac_id=261)
wherefeatureid in (120); 
                                            QUERY PLAN
--------------------------------------------------------------------------------------------------
 Nested Loop  (cost=1.03..59.90 rows=1 width=16)
   ->  Bitmap Heap Scan on point_features upf  (cost=1.03..11.50 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..4.83 rows=1 width=8)
         Index Cond: ((b.entity_id = "outer".entity_id) AND (b.fac_id = 261))
(7 rows)

regression=# 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..127.70 rows=10 width=16)
   ->  Bitmap Heap Scan on point_features upf  (cost=1.03..11.50 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..11.50 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)

(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 ...)