From: Doug Eck <deck1@yahoo.com>
To: pgsql-performance@postgresql.org
Subject: Re: Identical DB's, different execution plans
Date: Mon, 29 Sep 2008 11:18:45 -0700 (PDT)
Message-ID: <822909.77097.qm@web45203.mail.sp1.yahoo.com> (raw)
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 <tgl@sss.pgh.pa.us>
To: Doug Eck <deck1@yahoo.com>
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 <deck1@yahoo.com> 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: deck1@yahoo.com
Subject: Re: Identical DB's, different execution plans
In-Reply-To: <822909.77097.qm@web45203.mail.sp1.yahoo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox