postgresql - 为什么 Postgres 中的错误行估计很慢？

Question

是什么让坏行估计成为 SQL 查询性能中的痛点？我很想知道其中的内部原因。

通常错误的行估计实际上会选择正确的计划，好查询和坏查询之间的唯一区别是估计的行数。

为什么经常会出现如此巨大的性能差异？

是因为Postgres使用行估计来分配内存吗？

Answer 1

Postgresql 优化器是一个基于成本的优化器(CBO)，查询将从执行计划中以最小的成本执行，成本将通过表的统计来计算。

Why are bad row estimates slow in Postgres?

因为错误的统计可能会选择错误的执行计划。这是一个例子

有两个表，T1有20000000行，T2有1000000行。

CREATE TABLE T1 (
    ID INT NOT NULL PRIMARY KEY,
    val INT NOT NULL,
    col1 UUID NOT NULL,
    col2 UUID NOT NULL,
    col3 UUID NOT NULL,
    col4 UUID NOT NULL,
    col5 UUID NOT NULL,
    col6 UUID NOT NULL
);


INSERT INTO T1
SELECT i,
       RANDOM() * 1000000,
       md5(random()::text || clock_timestamp()::text)::uuid,
       md5(random()::text || clock_timestamp()::text)::uuid,
       md5(random()::text || clock_timestamp()::text)::uuid,
       md5(random()::text || clock_timestamp()::text)::uuid,
       md5(random()::text || clock_timestamp()::text)::uuid,
       md5(random()::text || clock_timestamp()::text)::uuid
FROM generate_series(1,20000000) i;


CREATE TABLE T2 (
    ID INT NOT NULL PRIMARY KEY,
    val INT NOT NULL,
    col1 UUID NOT NULL,
    col2 UUID NOT NULL,
    col3 UUID NOT NULL,
    col4 UUID NOT NULL,
    col5 UUID NOT NULL,
    col6 UUID NOT NULL
);

INSERT INTO T2
SELECT i,
       RANDOM() * 1000000,
       md5(random()::text || clock_timestamp()::text)::uuid,
       md5(random()::text || clock_timestamp()::text)::uuid,
       md5(random()::text || clock_timestamp()::text)::uuid,
       md5(random()::text || clock_timestamp()::text)::uuid,
       md5(random()::text || clock_timestamp()::text)::uuid,
       md5(random()::text || clock_timestamp()::text)::uuid
FROM generate_series(1,1000000) i;

当我们对表进行join时，我们将得到一个可能使用Merge JOIN的执行计划

EXPLAIN (ANALYZE,TIMING ON,BUFFERS ON)
SELECT t1.*
FROM T1 
INNER JOIN T2 ON t1.id = t2.id 
WHERE t1.id < 1000000 

"Gather  (cost=1016.37..30569.85 rows=53968 width=104) (actual time=0.278..837.297 rows=999999 loops=1)"
"  Workers Planned: 2"
"  Workers Launched: 2"
"  Buffers: shared hit=38273 read=21841"
"  ->  Merge Join  (cost=16.37..24173.05 rows=22487 width=104) (actual time=11.993..662.770 rows=333333 loops=3)"
"        Merge Cond: (t2.id = t1.id)"
"        Buffers: shared hit=38273 read=21841"
"        ->  Parallel Index Only Scan using t2_pkey on t2  (cost=0.42..20147.09 rows=416667 width=4) (actual time=0.041..69.947 rows=333333 loops=3)"
"              Heap Fetches: 0"
"              Buffers: shared hit=6 read=2732"
"        ->  Index Scan using t1_pkey on t1  (cost=0.44..48427.24 rows=1079360 width=104) (actual time=0.041..329.874 rows=999819 loops=3)"
"              Index Cond: (id < 1000000)"
"              Buffers: shared hit=38267 read=19109"
"Planning:"
"  Buffers: shared hit=4 read=8"
"Planning Time: 0.228 ms"
"Execution Time: 906.760 ms"

但是当我如下更新很多行时让 id 加上 100000000 当 id 小于 1000000

update T1
set id = id + 100000000
where id < 1000000

我们再次使用相同的查询，它将使用Merge JOIN，但应该有另一个更好的选择而不是Merge JOIN。

如果您没有达到 autovacuum_analyze_threshold(autovacuum_analyze_threshold 默认值为 0.1 这意味着我们需要创建超过 10% 死元组 postgresql会自动更新统计)

EXPLAIN (ANALYZE,TIMING ON,BUFFERS ON)
SELECT t1.*
FROM T1 
INNER JOIN T2 ON t1.id = t2.id 
WHERE t1.id < 1000000 

"Gather  (cost=1016.37..30707.83 rows=53968 width=104) (actual time=51.403..55.517 rows=0 loops=1)"
"  Workers Planned: 2"
"  Workers Launched: 2"
"  Buffers: shared hit=8215"
"  ->  Merge Join  (cost=16.37..24311.03 rows=22487 width=104) (actual time=6.736..6.738 rows=0 loops=3)"
"        Merge Cond: (t2.id = t1.id)"
"        Buffers: shared hit=8215"
"        ->  Parallel Index Only Scan using t2_pkey on t2  (cost=0.42..20147.09 rows=416667 width=4) (actual time=0.024..0.024 rows=1 loops=3)"
"              Heap Fetches: 0"
"              Buffers: shared hit=8"
"        ->  Index Scan using t1_pkey on t1  (cost=0.44..50848.71 rows=1133330 width=104) (actual time=6.710..6.710 rows=0 loops=3)"
"              Index Cond: (id < 1000000)"
"              Buffers: shared hit=8207"
"Planning:"
"  Buffers: shared hit=2745"
"Planning Time: 3.938 ms"
"Execution Time: 55.550 ms"

当我们使用手动ANALYZE T1; 意味着更新T1 表统计信息，然后再次查询时，查询将得到Nested Loop 更好比 Merge JOIN

"QUERY PLAN"
"Nested Loop  (cost=0.86..8.90 rows=1 width=104) (actual time=0.004..0.004 rows=0 loops=1)"
"  Buffers: shared hit=3"
"  ->  Index Scan using t1_pkey on t1  (cost=0.44..4.46 rows=1 width=104) (actual time=0.003..0.003 rows=0 loops=1)"
"        Index Cond: (id < 1000000)"
"        Buffers: shared hit=3"
"  ->  Index Only Scan using t2_pkey on t2  (cost=0.42..4.44 rows=1 width=4) (never executed)"
"        Index Cond: (id = t1.id)"
"        Heap Fetches: 0"
"Planning:"
"  Buffers: shared hit=20"
"Planning Time: 0.232 ms"
"Execution Time: 0.027 ms"

小结:

表中精确的统计信息将帮助优化器通过表中精确的COST得到正确的执行计划。

这是一个脚本，可以帮助我们最后一次搜索 last_analyze & last_vacuum。

SELECT
  schemaname, relname,
  last_vacuum, last_autovacuum,
  vacuum_count, autovacuum_count,
  last_analyze,last_autoanalyze
FROM pg_stat_user_tables
where relname = 'tablename';