On Fri, 2006-04-14 at 12:13 -0400, Tom Lane wrote:
> Ian Westmacott <ianw@xxxxxxxxxxxxxx> 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@xxxxxxxxxxxxxx>
IntelliVid Corp.
