I have this query, where PG (8.1.2) prefers Merge Join over Hash Join
over Nested Loop. However, this order turns out to increase in
performance. I was hoping someone might be able to shed some light on
why PG chooses the plans in this order, and what I might do to
influence it otherwise. Thanks,
itvtrackdata=> explain analyze SELECT mva,blob.* FROM
blobs00000000000033c3_c16010 AS blob NATURAL JOIN
objects00000000000033c3 WHERE
AREA(BOX(POINT(bbox_x1,bbox_y0),POINT(bbox_x0,bbox_y1))#BOX('(50,10),(10,50)'))/AREA(BOX(POINT(bbox_x1,bbox_y0),POINT(bbox_x0,bbox_y1)))>0 AND time>=1263627787-32768 AND time<1273458187 AND finish-start>=8738 ORDER BY time ASC;
QUERY
PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=47170.44..47184.46 rows=5607 width=28) (actual
time=2661.980..2663.642 rows=1687 loops=1)
Sort Key: blob."time"
-> Merge Join (cost=46547.88..46821.32 rows=5607 width=28) (actual
time=2645.685..2657.621 rows=1687 loops=1)
Merge Cond: ("outer".sva = "inner".sva)
-> Sort (cost=18003.31..18045.36 rows=16821 width=20) (actual
time=181.303..183.092 rows=1741 loops=1)
Sort Key: blob.sva
-> Bitmap Heap Scan on blobs00000000000033c3_c16010 blob
(cost=535.77..16822.65 rows=16821 width=20) (actual time=10.827..177.671
rows=1741 loops=1)
Recheck Cond: (("time" >= 1263595019) AND ("time" <
1273458187))
Filter: ((area((box(point((bbox_x1)::double
precision, (bbox_y0)::double precision), point((bbox_x0)::double
precision, (bbox_y1)::double precision)) # '(50,50),(10,10)'::box)) /
area(box(point((bbox_x1)::double precision, (bbox_y0)::double
precision), point((bbox_x0)::double precision, (bbox_y1)::double
precision)))) > 0::double precision)
-> Bitmap Index Scan on
idx_blobs00000000000033c3_c16010_time (cost=0.00..535.77 rows=50462
width=0) (actual time=8.565..8.565 rows=50673 loops=1)
Index Cond: (("time" >= 1263595019) AND
("time" < 1273458187))
-> Sort (cost=28544.56..28950.88 rows=162526 width=16)
(actual time=2387.726..2437.429 rows=29969 loops=1)
Sort Key: objects00000000000033c3.sva
-> Seq Scan on objects00000000000033c3
(cost=0.00..14477.68 rows=162526 width=16) (actual time=0.085..826.125
rows=207755 loops=1)
Filter: ((finish - "start") >= 8738)
Total runtime: 2675.037 ms
(16 rows)
itvtrackdata=> set enable_mergejoin to false;
SET
itvtrackdata=> explain analyze SELECT mva,blob.* FROM
blobs00000000000033c3_c16010 AS blob NATURAL JOIN
objects00000000000033c3 WHERE
AREA(BOX(POINT(bbox_x1,bbox_y0),POINT(bbox_x0,bbox_y1))#BOX('(50,10),(10,50)'))/AREA(BOX(POINT(bbox_x1,bbox_y0),POINT(bbox_x0,bbox_y1)))>0 AND time>=1263627787-32768 AND time<1273458187 AND finish-start>=8738 ORDER BY time ASC;
QUERY
PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=65783.09..65797.10 rows=5607 width=28) (actual
time=1211.228..1212.671 rows=1687 loops=1)
Sort Key: blob."time"
-> Hash Join (cost=15419.77..65433.97 rows=5607 width=28) (actual
time=1067.514..1207.727 rows=1687 loops=1)
Hash Cond: ("outer".sva = "inner".sva)
-> Bitmap Heap Scan on blobs00000000000033c3_c16010 blob
(cost=535.77..16822.65 rows=16821 width=20) (actual time=14.720..149.179
rows=1741 loops=1)
Recheck Cond: (("time" >= 1263595019) AND ("time" <
1273458187))
Filter: ((area((box(point((bbox_x1)::double precision,
(bbox_y0)::double precision), point((bbox_x0)::double precision,
(bbox_y1)::double precision)) # '(50,50),(10,10)'::box)) /
area(box(point((bbox_x1)::double precision, (bbox_y0)::double
precision), point((bbox_x0)::double precision, (bbox_y1)::double
precision)))) > 0::double precision)
-> Bitmap Index Scan on
idx_blobs00000000000033c3_c16010_time (cost=0.00..535.77 rows=50462
width=0) (actual time=12.880..12.880 rows=50673 loops=1)
Index Cond: (("time" >= 1263595019) AND ("time" <
1273458187))
-> Hash (cost=14477.68..14477.68 rows=162526 width=16)
(actual time=1052.729..1052.729 rows=207755 loops=1)
-> Seq Scan on objects00000000000033c3
(cost=0.00..14477.68 rows=162526 width=16) (actual time=0.028..684.047
rows=207755 loops=1)
Filter: ((finish - "start") >= 8738)
Total runtime: 1217.938 ms
(13 rows)
itvtrackdata=> set enable_hashjoin to false;
SET
itvtrackdata=> explain analyze SELECT mva,blob.* FROM
blobs00000000000033c3_c16010 AS blob NATURAL JOIN
objects00000000000033c3 WHERE
AREA(BOX(POINT(bbox_x1,bbox_y0),POINT(bbox_x0,bbox_y1))#BOX('(50,10),(10,50)'))/AREA(BOX(POINT(bbox_x1,bbox_y0),POINT(bbox_x0,bbox_y1)))>0 AND time>=1263627787-32768 AND time<1273458187 AND finish-start>=8738 ORDER BY time ASC;
QUERY
PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=118258.49..118272.51 rows=5607 width=28) (actual
time=197.204..198.871 rows=1687 loops=1)
Sort Key: blob."time"
-> Nested Loop (cost=535.77..117909.37 rows=5607 width=28) (actual
time=27.560..192.526 rows=1687 loops=1)
-> Bitmap Heap Scan on blobs00000000000033c3_c16010 blob
(cost=535.77..16822.65 rows=16821 width=20) (actual time=27.450..157.266
rows=1741 loops=1)
Recheck Cond: (("time" >= 1263595019) AND ("time" <
1273458187))
Filter: ((area((box(point((bbox_x1)::double precision,
(bbox_y0)::double precision), point((bbox_x0)::double precision,
(bbox_y1)::double precision)) # '(50,50),(10,10)'::box)) /
area(box(point((bbox_x1)::double precision, (bbox_y0)::double
precision), point((bbox_x0)::double precision, (bbox_y1)::double
precision)))) > 0::double precision)
-> Bitmap Index Scan on
idx_blobs00000000000033c3_c16010_time (cost=0.00..535.77 rows=50462
width=0) (actual time=24.445..24.445 rows=50673 loops=1)
Index Cond: (("time" >= 1263595019) AND ("time" <
1273458187))
-> Index Scan using idx_objects00000000000033c3_sva on
objects00000000000033c3 (cost=0.00..6.00 rows=1 width=16) (actual
time=0.013..0.015 rows=1 loops=1741)
Index Cond: ("outer".sva = objects00000000000033c3.sva)
Filter: ((finish - "start") >= 8738)
Total runtime: 200.719 ms
(12 rows)
--
Ian Westmacott <ianw@xxxxxxxxxxxxxx>
IntelliVid Corp.
