Subject: Re: merge>hash>loop
From: Ian Westmacott <ianw@intellivid.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-performance@postgresql.org
In-Reply-To: <10000.1145031205@sss.pgh.pa.us>
References: <1145029009.3422.31.camel@spectre.intellivid.com>
	<10000.1145031205@sss.pgh.pa.us>
Content-Type: text/plain
Organization: IntelliVid Corp.
Date: Fri, 14 Apr 2006 12:45:48 -0400
Message-Id: <1145033149.3422.41.camel@spectre.intellivid.com>
Mime-Version: 1.0
Content-Transfer-Encoding: 7bit

On Fri, 2006-04-14 at 12:13 -0400, Tom Lane wrote:
> Ian Westmacott <ianw@intellivid.com> writes:
> > 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,
> 
> Reducing random_page_cost would influence it to prefer the nestloop.
> However, I doubt you're ever going to get really ideal results for
> this query --- the estimated row counts are too far off, and the
> WHERE conditions are sufficiently bizarre that there's not much hope
> that they'd ever be much better.

That's what I feared, thanks.  But then if I simplify things a bit,
such that the row counts are quite good, and PG still chooses a
worse plan, can I conclude anything about my configuration settings
(like random_page_cost)?

itvtrackdata=> explain analyze SELECT mva,blob.* FROM
blobs00000000000033c3_c16010 AS blob NATURAL JOIN
objects00000000000033c3 WHERE time>=1263627787 AND time<1273458187 ORDER
BY time ASC;

QUERY
PLAN                                                                              
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=75426.47..75552.21 rows=50295 width=28) (actual
time=2083.624..2146.654 rows=50472 loops=1)
   Sort Key: blob."time"
   ->  Hash Join  (cost=16411.51..70749.84 rows=50295 width=28) (actual
time=1790.746..1910.204 rows=50472 loops=1)
         Hash Cond: ("outer".sva = "inner".sva)
         ->  Bitmap Heap Scan on blobs00000000000033c3_c16010 blob
(cost=533.77..14421.19 rows=50295 width=20) (actual time=12.533..78.284
rows=50472 loops=1)
               Recheck Cond: (("time" >= 1263627787) AND ("time" <
1273458187))
               ->  Bitmap Index Scan on
idx_blobs00000000000033c3_c16010_time  (cost=0.00..533.77 rows=50295
width=0) (actual time=12.447..12.447 rows=50472 loops=1)
                     Index Cond: (("time" >= 1263627787) AND ("time" <
1273458187))
         ->  Hash  (cost=12039.79..12039.79 rows=487579 width=16)
(actual time=1618.128..1618.128 rows=487579 loops=1)
               ->  Seq Scan on objects00000000000033c3
(cost=0.00..12039.79 rows=487579 width=16) (actual time=0.019..833.931
rows=487579 loops=1)
 Total runtime: 2194.492 ms
(11 rows)

itvtrackdata=> set enable_hashjoin to false;
SET
itvtrackdata=> explain analyze SELECT mva,blob.* FROM
blobs00000000000033c3_c16010 AS blob NATURAL JOIN
objects00000000000033c3 WHERE time>=1263627787 AND time<1273458187 ORDER
BY time ASC;

QUERY
PLAN                                                                                
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=321096.91..321222.64 rows=50295 width=28) (actual
time=929.893..992.316 rows=50472 loops=1)
   Sort Key: blob."time"
   ->  Nested Loop  (cost=533.77..316420.28 rows=50295 width=28) (actual
time=16.780..719.140 rows=50472 loops=1)
         ->  Bitmap Heap Scan on blobs00000000000033c3_c16010 blob
(cost=533.77..14421.19 rows=50295 width=20) (actual time=16.693..84.299
rows=50472 loops=1)
               Recheck Cond: (("time" >= 1263627787) AND ("time" <
1273458187))
               ->  Bitmap Index Scan on
idx_blobs00000000000033c3_c16010_time  (cost=0.00..533.77 rows=50295
width=0) (actual time=16.546..16.546 rows=50472 loops=1)
                     Index Cond: (("time" >= 1263627787) AND ("time" <
1273458187))
         ->  Index Scan using idx_objects00000000000033c3_sva on
objects00000000000033c3  (cost=0.00..5.99 rows=1 width=16) (actual
time=0.006..0.008 rows=1 loops=50472)
               Index Cond: ("outer".sva = objects00000000000033c3.sva)
 Total runtime: 1039.725 ms
(10 rows)

-- 
Ian Westmacott <ianw@intellivid.com>
IntelliVid Corp.