merge>hash>loop - Mailing list pgsql-performance

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
ANDtime>=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
ANDtime>=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
ANDtime>=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@intellivid.com>
IntelliVid Corp.