From: Tom Lane <tgl@sss.pgh.pa.us>
To: rm_pg@cheapcomplexdevices.com
Cc: pgsql-performance@postgresql.org
Subject: Re: Missed index opportunity for outer join?
Date: Mon, 05 Dec 2005 17:38:19 -0500
Message-ID: <3267.1133822299@sss.pgh.pa.us> (raw)
In-Reply-To: <Pine.LNX.4.58.0512051401090.21335@greenie.cheapcomplexdevices.com>
References: <Pine.LNX.4.58.0512051401090.21335@greenie.cheapcomplexdevices.com>
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) where featureid 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 ...)
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, rm_pg@cheapcomplexdevices.com
Subject: Re: Missed index opportunity for outer join?
In-Reply-To: <3267.1133822299@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox