Setting enable_nestloop = off did result in a hash join, so I also set
enable_hashjoin = off.
The new plan from the slower db:
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------
Merge Join (cost=20195.54..46442.99 rows=7876 width=47) (actual
time=136.531..478.708 rows=8437 loops=1)
Merge Cond: ((t1.bn)::text = "inner"."?column3?")
-> Index Scan using t1_uc2 on t1 (cost=0.00..25604.74 rows=204906
width=32) (actual time=0.061..327.285 rows=8438 loops=1)
Filter: active
-> Sort (cost=20195.54..20222.79 rows=10898 width=22) (actual
time=136.461..138.621 rows=12204 loops=1)
Sort Key: (t2.sn)::text
-> Bitmap Heap Scan on t2 (cost=145.12..19464.74 rows=10898
width=22) (actual time=7.580..120.144 rows=12204 loops=1)
Recheck Cond: (eff_dt = ('now'::text)::date)
-> Bitmap Index Scan on t2_nu1 (cost=0.00..142.40 rows=10898
width=0) (actual time=4.964..4.964 rows=24483 loops=1)
Index Cond: (eff_dt = ('now'::text)::date)
Total runtime: 480.344 ms
(11 rows)
And the faster one:
QUERY
PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------
Merge Join (cost=21371.63..21720.78 rows=7270 width=47) (actual
time=60.435..80.604 rows=8437 loops=1)
Merge Cond: ("outer"."?column6?" = "inner"."?column3?")
-> Sort (cost=8988.56..9100.55 rows=44794 width=32) (actual
time=30.498..33.093 rows=8438 loops=1)
Sort Key: (t1.bn)::text
-> Seq Scan on t1 (cost=0.00..5528.00 rows=44794 width=32) (actual
time=0.010..17.950 rows=8439 loops=1)
Filter: active
-> Sort (cost=12383.07..12409.32 rows=10500 width=22) (actual
time=29.928..33.658 rows=12204 loops=1)
Sort Key: (t2.sn)::text
-> Index Scan using t2_nu1 on t2 (cost=0.00..11681.77 rows=10500
width=22) (actual time=0.062..13.356 rows=12204 loops=1)
Index Cond: (eff_dt = ('now'::text)::date)
Total runtime: 83.054 ms
(11 rows)
And the query again:
explain analyze
select
t1.bn,
t2.mu,
t1.nm,
t1.root,
t1.suffix,
t1.type
from
t1,
t2
where
t2.eff_dt = current_date
and t1.active = true
and t1.bn = t2.sn;
Thanks.
----- Original Message ----
From: Tom Lane <[EMAIL PROTECTED]>
To: Doug Eck <[EMAIL PROTECTED]>
Cc: pgsql-performance@postgresql.org
Sent: Monday, September 29, 2008 11:42:01 AM
Subject: Re: [PERFORM] Identical DB's, different execution plans
Doug Eck <[EMAIL PROTECTED]> writes:
> Any ideas as to what could the first db to opt for the slower subquery rather
> than the merge?
Not from the information given. Presumably db1 thinks that the
mergejoin plan would be slower, but why it thinks that isn't clear yet.
Try setting enable_nestloop = off (and enable_hashjoin = off if it then
wants a hashjoin) and then post the EXPLAIN ANALYZE results.
regards, tom lane
