PostgreSQL EXPLAIN ANALYZE 估计行数和实际行数差很多怎么办:过期统计信息、列相关、数据倾斜和 pg_stat_statements

    HashAggregate  (cost=19224.70..19224.74 rows=4 width=21) (actual time=78.070..78.072 rows=4 loops=1)
      Group Key: c.region
      Batches: 1  Memory Usage: 24kB
      Buffers: shared hit=5504
      ->  Hash Join  (cost=289.00..18487.23 rows=98330 width=9) (actual time=1.242..67.849 rows=99360 loops=1)
            Hash Cond: (o.customer_id = c.id)
            Buffers: shared hit=5504
            ->  Seq Scan on orders o  (cost=0.00..17940.00 rows=98330 width=8) (actual time=0.008..53.678 rows=99360 loops=1)
                  Filter: (amount > 900)
                  Rows Removed by Filter: 900640
                  Buffers: shared hit=5440
            ->  Hash  (cost=164.00..164.00 rows=10000 width=9) (actual time=1.228..1.228 rows=10000 loops=1)
                  Buckets: 16384  Batches: 1  Memory Usage: 558kB
                  Buffers: shared hit=64
                  ->  Seq Scan on customers c  (cost=0.00..164.00 rows=10000 width=9) (actual time=0.004..0.582 rows=10000 loops=1)
                        Buffers: shared hit=64
    Planning:
      Buffers: shared hit=85 read=15
    Planning Time: 0.324 ms
    Execution Time: 78.118 ms

  没有 ANALYZE:e 的扫描节点估计 1 行、实际 900000 行;总耗时 767.68 ms
      Aggregate  (rows=1 width=8) (actual time=767.663..767.664 rows=1 loops=1)
        ->  Nested Loop  (rows=1 width=0) (actual time=0.016..742.781 rows=900000 loops=1)
              ->  Index Only Scan using e_kind_idx on e  (rows=1 width=2) (actual time=0.011..99.054 rows=900000 loops=1)
                    Index Cond: (kind = 'b'::text)
                    Heap Fetches: 900000
              ->  Seq Scan on small s  (rows=1 width=2) (actual time=0.000..0.000 rows=1 loops=900000)
  ANALYZE 之后:e 的扫描节点估计 898700 行、实际 900000 行;总耗时 109.37 ms
      Aggregate  (rows=1 width=8) (actual time=109.359..109.359 rows=1 loops=1)
        ->  Nested Loop  (rows=898700 width=0) (actual time=4.754..89.119 rows=900000 loops=1)
              ->  Seq Scan on small s  (rows=1 width=2) (actual time=0.005..0.005 rows=1 loops=1)
                    Filter: (kind = 'b'::text)
                    Rows Removed by Filter: 1
              ->  Seq Scan on e  (rows=898700 width=2) (actual time=4.749..54.231 rows=900000 loops=1)

  没有扩展统计:估计 1030 行、实际 10000 行
  CREATE STATISTICS … (dependencies) 并 ANALYZE 之后:估计 10200 行、实际 10000 行

  实际行数:v37 有 3885 行,v300 有 464 行
  statistics target = 10   v37:估计 668 行(实际 3885);v300:估计 668 行(实际 464)
  statistics target = 100  v37:估计 4633 行(实际 3885);v300:估计 368 行(实际 464)
  statistics target = 1000 v37:估计 3997 行(实际 3885);v300:估计 443 行(实际 464)

  调用  200 次,总耗时   8445.9 ms,平均   42.230 ms,共返回   200 行  select * from orders where id = $1
  调用   20 次,总耗时    759.9 ms,平均   37.996 ms,共返回    20 行  select count(*), sum(amount) from orders where amount > $1
  调用    3 次,总耗时    437.0 ms,平均  145.666 ms,共返回    12 行  select c.region, count(*) from orders o join customers c on c.id = o.customer_id
  排在最前面的那条按 id 查 orders,orders.id 上没有索引,每次都是全表扫描。建索引后重跑同样的 200 次:
  调用  200 次,总耗时      0.6 ms,平均    0.003 ms