Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 13D1037BD97 for ; Mon, 29 Sep 2008 15:18:51 -0300 (ADT) Received: from postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 04573-08 for ; Mon, 29 Sep 2008 15:18:47 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from n79.bullet.mail.sp1.yahoo.com (n79.bullet.mail.sp1.yahoo.com [98.136.44.39]) by postgresql.org (Postfix) with SMTP id 5251437BA5B for ; Mon, 29 Sep 2008 15:18:47 -0300 (ADT) Received: from [216.252.122.218] by n79.bullet.mail.sp1.yahoo.com with NNFMP; 29 Sep 2008 18:18:45 -0000 Received: from [69.147.65.157] by t3.bullet.sp1.yahoo.com with NNFMP; 29 Sep 2008 18:18:45 -0000 Received: from [127.0.0.1] by omp405.mail.sp1.yahoo.com with NNFMP; 29 Sep 2008 18:18:45 -0000 X-Yahoo-Newman-Property: ymail-3 X-Yahoo-Newman-Id: 913991.58477.bm@omp405.mail.sp1.yahoo.com Received: (qmail 77407 invoked by uid 60001); 29 Sep 2008 18:18:45 -0000 DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com; h=X-YMail-OSG:Received:X-Mailer:Date:From:Reply-To:Subject:To:MIME-Version:Content-Type:Message-ID; b=wuKH5q8MKi5QoKW2sPedwBxAHtJtRkLYq0lU3JjCI1jKvs+iJTOUoH/b3o/BynkwliI0O8TLuAAZylH4JpnorXSkphfMQONW7KwEdhbAq2LKwxnx3y4OBrXw6dYSGqos7y54yao4VpuA/mMvk6vy+mnCIjifBBlSn0DltVAvHB4=; X-YMail-OSG: WQWMt4sVM1m9LiYTvoUfzUqISDPmvtiTKlEoTP0g6eEbpWSRu86NEEhFyXwUBaj3A7NDHa2GRF3UQgc.7ckk3h3mc8as8MsSWMi5wv.6zZd9u1A6RBM1ykytFceOdR0M9XNrwOG5HE21V76y.KKWUFnXO0NbWKzD0CfsW4Gx8J1s41ONCA-- Received: from [64.126.29.122] by web45203.mail.sp1.yahoo.com via HTTP; Mon, 29 Sep 2008 11:18:45 PDT X-Mailer: YahooMailRC/1096.28 YahooMailWebService/0.7.218.2 Date: Mon, 29 Sep 2008 11:18:45 -0700 (PDT) From: Doug Eck Reply-To: Doug Eck Subject: Re: Identical DB's, different execution plans To: pgsql-performance@postgresql.org MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="0-1298386203-1222712325=:77097" Message-ID: <822909.77097.qm@web45203.mail.sp1.yahoo.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.001 tagged_above=0 required=5 tests=HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 200809/266 X-Sequence-Number: 31579 --0-1298386203-1222712325=:77097 Content-Type: text/plain; charset=us-ascii 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 To: Doug Eck 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 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 --0-1298386203-1222712325=:77097 Content-Type: text/html; charset=us-ascii
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

--0-1298386203-1222712325=:77097--