Re: Identical DB's, different execution plans - Mailing list pgsql-performance

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.