I have a case where an outer join's taking 10X more time than
a non-outer join; and it looks to me like the outer join could
have taken advantage of the same indexes that the non-outer join did.
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.
Any reason it couldn't have use the index there?
Also - in both cases the second part of the nested loop
is using the same multi-column index on the table "facets".
The non-outer-join uses both columns of this multi-column index.
The outer-join only uses one of the columns and is much slower.
Any reason it couldn't have use both columns of the index there?
Attached below are explain analyze for the slow outer join
and the fast non-outer join. This is using 8.1.0.
Thanks in advance,
Ron
===============================================================================
== The outer join - slow
===============================================================================
fli=# explain analyze select * from userfeatures.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.11..90317.33 rows=1207 width=505) (actual time=8.985..734.761 rows=917 loops=1)
-> Seq Scan on point_features upf (cost=0.00..265.85 rows=948 width=80) (actual time=8.792..14.270 rows=917 loops=1)
Filter: (featureid = 120)
-> Bitmap Heap Scan on facets b (cost=2.11..94.60 rows=31 width=425) (actual time=0.101..0.770 rows=1 loops=917)
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..2.11 rows=31 width=0) (actual time=0.067..0.067 rows=32 loops=917)
Index Cond: (b.entity_id = "outer".entity_id)
Total runtime: 736.444 ms
(9 rows)
===============================================================================
== The non-outer join - fast
===============================================================================
fli=# explain analyze select * from userfeatures.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=23.32..4942.48 rows=1207 width=505) (actual time=0.571..55.867 rows=917 loops=1)
-> Bitmap Heap Scan on point_features upf (cost=23.32..172.17 rows=948 width=80) (actual time=0.468..2.226 rows=917 loops=1)
Recheck Cond: (featureid = 120)
-> Bitmap Index Scan on point_features__featureid (cost=0.00..23.32 rows=948 width=0) (actual time=0.413..0.413 rows=917 loops=1)
Index Cond: (featureid = 120)
-> Index Scan using "fac_val(entity_id,fac_id)" on facets b (cost=0.00..5.02 rows=1 width=425) (actual time=0.051..0.053 rows=1 loops=917)
Index Cond: ((b.entity_id = "outer".entity_id) AND (b.fac_id = 261))
Total runtime: 56.892 ms
(8 rows)
===============================================================================
== The tables involved.
===============================================================================
fli=# \d facets
Table "facet.facets"
Column | Type | Modifiers
-----------+---------+-----------
entity_id | integer |
nam_hash | integer |
val_hash | integer |
fac_id | integer |
dis_id | integer |
fac_val | text |
fac_ival | integer |
fac_tval | text |
fac_nval | numeric |
fac_raval | real[] |
fac_bval | bytea |
Indexes:
"fac_val(entity_id,fac_id)" btree (entity_id, fac_id)
"facets__dis_id" btree (dis_id)
"facets__ent_id" btree (entity_id)
"facets__fac_id" btree (fac_id)
"facets__id_value" btree (fac_id, fac_val) CLUSTER
Foreign-key constraints:
"facets_entity_id_fkey" FOREIGN KEY (entity_id) REFERENCES entity(entity_id) ON DELETE CASCADE
"facets_fac_id_fkey" FOREIGN KEY (fac_id) REFERENCES facet_lookup(fac_id) ON DELETE CASCADE
fli=# \d point_features
Table "userfeatures.point_features"
Column | Type | Modifiers
-----------+----------+------------------------------------------------------------------
pointid | integer | not null default nextval('point_features_pointid_seq'::regclass)
entity_id | integer |
featureid | integer |
sessionid | integer |
userid | integer |
extid | text |
label | text |
iconid | integer |
the_geom | geometry |
Indexes:
"point_features__featureid" btree (featureid)
"point_features__postgis" gist (the_geom)
Check constraints:
"enforce_dims_the_geom" CHECK (ndims(the_geom) = 2)
"enforce_geotype_the_geom" CHECK (geometrytype(the_geom) = 'POINT'::text OR the_geom IS NULL)
"enforce_srid_the_geom" CHECK (srid(the_geom) = -1)
===============================================================================
== version info
===============================================================================
fli=# select version();
version
-------------------------------------------------------------------------------------
PostgreSQL 8.1.0 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.3.3 (SuSE Linux)
(1 row)
