postgresql - 为什么我为同一个查询获得不同的查询计划(在主服务器/复制服务器上)

Question

我在主服务器和复制服务器上运行相同的查询。

postgresql.conf 中两台服务器的设置相同，但主/写服务器有 32GB RAM，复制/读取服务器有 16GB RAM。

这是查询:

EXPLAIN ANALYZE
SELECT 
    m.sale_date,b.branch_name,
    m.transaction_type,
    TRIM(both ' '  from UPPER(l.master_code)) as stockcode, 
    (l.selling_price * l.quantity) * (1 + l.tax_percentage /100) as selling_price,l.quantity 
FROM 
    transaction_master m 
LEFT OUTER JOIN 
    transaction_line_items l ON m.guid = l.link_guid 
INNER JOIN 
    branch_details b ON m.branch_code = b.branch_code 
WHERE 
    m.sale_date BETWEEN '2015-12-22' AND '2015-12-22' 
AND 
    (m.transaction_type = 'POSSALE' OR m.transaction_type = 'POSCN' OR         m.transaction_type = 'POSREF') 
ORDER BY 
    sale_date DESC nulls LAST

在写入服务器上，总时间为 14 秒:

Sort  (cost=1625242.60..1625417.63 rows=70012 width=50) (actual time=14732.852..14734.362 rows=72522 loops=1)
  Sort Key: m.sale_date
  Sort Method: quicksort  Memory: 11496kB
  ->  Hash Right Join  (cost=90258.98..1619608.28 rows=70012 width=50) (actual time=11202.434..14710.938 rows=72522 loops=1)
        Hash Cond: ((l.link_guid)::text = (m.guid)::text)
        ->  Seq Scan on transaction_line_items l  (cost=0.00..1258955.88 rows=23879388 width=62) (actual time=0.050..6612.397 rows=25147461 loops=1)
        ->  Hash  (cost=89903.39..89903.39 rows=28447 width=62) (actual time=46.941..46.941 rows=42504 loops=1)
              Buckets: 4096  Batches: 1  Memory Usage: 3948kB
              ->  Hash Join  (cost=4088.79..89903.39 rows=28447 width=62) (actual time=12.460..37.283 rows=42504 loops=1)
                    Hash Cond: ((m.branch_code)::text = (b.branch_code)::text)
                    ->  Bitmap Heap Scan on transaction_master m  (cost=4057.12..89480.57 rows=28447 width=52) (actual time=12.240..21.802 rows=42504 loops=1)
                          Recheck Cond: (((sale_date >= '2015-12-22'::date) AND (sale_date <= '2015-12-22'::date) AND ((transaction_type)::text = 'POSSALE'::text)) OR ((sale_date >= '2015-12-22'::date) AND (sale_date <= '2015-12-22'::date) AND ((transaction_type)::text = 'POSCN'::text)) OR ((sale_date >= '2015-12-22'::date) AND (sale_date <= '2015-12-22'::date) AND ((transaction_type)::text = 'POSREF'::text)))
                          Heap Blocks: exact=1095
                          ->  BitmapOr  (cost=4057.12..4057.12 rows=29508 width=0) (actual time=12.112..12.112 rows=0 loops=1)
                                ->  Bitmap Index Scan on tm_datebranchtype  (cost=0.00..1345.26 rows=28130 width=0) (actual time=6.013..6.013 rows=40615 loops=1)
                                      Index Cond: ((sale_date >= '2015-12-22'::date) AND (sale_date <= '2015-12-22'::date) AND ((transaction_type)::text = 'POSSALE'::text))
                                ->  Bitmap Index Scan on tm_datebranchtype  (cost=0.00..1345.26 rows=1 width=0) (actual time=3.010..3.010 rows=0 loops=1)
                                  Index Cond: ((sale_date >= '2015-12-22'::date) AND (sale_date <= '2015-12-22'::date) AND ((transaction_type)::text = 'POSCN'::text))
                                ->  Bitmap Index Scan on tm_datebranchtype  (cost=0.00..1345.26 rows=1378 width=0) (actual time=3.087..3.087 rows=1889 loops=1)
                                      Index Cond: ((sale_date >= '2015-12-22'::date) AND (sale_date <= '2015-12-22'::date) AND ((transaction_type)::text = 'POSREF'::text))
                    ->  Hash  (cost=25.19..25.19 rows=519 width=18) (actual time=0.208..0.208 rows=519 loops=1)
                          Buckets: 1024  Batches: 1  Memory Usage: 26kB
                          ->  Seq Scan on branch_details b  (cost=0.00..25.19 rows=519 width=18) (actual time=0.006..0.119 rows=519 loops=1)
Planning time: 2.205 ms
Execution time: 14737.148 ms

复制/读取服务器的结果需要 111 秒:

Sort  (cost=3792870.57..3793045.60 rows=70012 width=50) (actual time=111432.945..111436.554 rows=72522 loops=1)
  Sort Key: m.sale_date
  Sort Method: quicksort  Memory: 11496kB
  ->  Hash Join  (cost=95177.21..3787236.25 rows=70012 width=50) (actual time=62904.693..111410.002 rows=72522 loops=1)
        Hash Cond: ((m.branch_code)::text = (b.branch_code)::text)
        ->  Hash Right Join  (cost=95052.11..3778797.22 rows=70012 width=40) (actual time=62894.976..111265.547 rows=72522 loops=1)
              Hash Cond: ((l.link_guid)::text = (m.guid)::text)
              ->  Seq Scan on transaction_line_items l  (cost=0.00..3408100.80 rows=23879388 width=62) (actual time=0.010..96694.745 rows=25147447 loops=1)
              ->  Hash  (cost=92136.29..92136.29 rows=28447 width=52) (actual time=76.786..76.786 rows=42504 loops=1)
                    Buckets: 4096  Batches: 1  Memory Usage: 3527kB
                    ->  Bitmap Heap Scan on transaction_master m  (cost=4057.12..92136.29 rows=28447 width=52) (actual time=28.078..59.330 rows=42504 loops=1)
                          Recheck Cond: (((sale_date >= '2015-12-22'::date) AND (sale_date <= '2015-12-22'::date) AND ((transaction_type)::text = 'POSSALE'::text)) OR ((sale_date >= '2015-12-22'::date) AND (sale_date <= '2015-12-22'::date) AND ((transaction_type)::text = 'POSCN'::text)) OR ((sale_date >= '2015-12-22'::date) AND (sale_date <= '2015-12-22'::date) AND ((transaction_type)::text = 'POSREF'::text)))
                          Heap Blocks: exact=1095
                          ->  BitmapOr  (cost=4057.12..4057.12 rows=29508 width=0) (actual time=27.824..27.824 rows=0 loops=1)
                                ->  Bitmap Index Scan on tm_datebranchtype  (cost=0.00..1345.26 rows=28130 width=0) (actual time=14.828..14.828 rows=40615 loops=1)
                                      Index Cond: ((sale_date >= '2015-12-22'::date) AND (sale_date <= '2015-12-22'::date) AND ((transaction_type)::text = 'POSSALE'::text))
                                ->  Bitmap Index Scan on tm_datebranchtype  (cost=0.00..1345.26 rows=1 width=0) (actual time=6.552..6.552 rows=0 loops=1)
                                      Index Cond: ((sale_date >= '2015-12-22'::date) AND (sale_date <= '2015-12-22'::date) AND ((transaction_type)::text = 'POSCN'::text))
                                ->  Bitmap Index Scan on tm_datebranchtype  (cost=0.00..1345.26 rows=1378 width=0) (actual time=6.439..6.439 rows=1889 loops=1)
                                      Index Cond: ((sale_date >= '2015-12-22'::date) AND (sale_date <= '2015-12-22'::date) AND ((transaction_type)::text = 'POSREF'::text))
        ->  Hash  (cost=71.90..71.90 rows=519 width=18) (actual time=0.457..0.457 rows=519 loops=1)
              Buckets: 1024  Batches: 1  Memory Usage: 26kB
              ->  Seq Scan on branch_details b  (cost=0.00..71.90 rows=519 width=18) (actual time=0.009..0.259 rows=519 loops=1)
Planning time: 4.147 ms
Execution time: 111441.566 ms

Answer 1

在#postgresql IRC channel 上获得一些帮助后，我发现造成差异的原因是两台服务器上 cpu_tuple_cost 的设置不同。

一旦我更改了设置并重新加载了 conf 文件，两台服务器上的查询计划就相同了。