京公网安备11010502063487号“PostgreSQL 明明建了索引,EXPLAIN 为什么还是 Seq Scan” 是最常见的性能问题。这篇在本机自己起一个 PostgreSQL 16.15,用 100 万行的表对比选择性、函数运算、排序规则和 random_page_cost 四类原因,每一条都给出计划和真实耗时。
where id = 500000:Index Scan using t_pkey on t (估计代价 8.44,估计行数 1) (实际行数 1);Execution Time 0.00 ms where k < 100000(约 10% 的行):Bitmap Heap Scan on t (估计代价 18425.30,估计行数 100389) (实际行数 99926);Execution Time 23.63 ms where k < 900000(约 90% 的行):Seq Scan on t (估计代价 27924.00,估计行数 899567) (实际行数 899462);Execution Time 68.77 ms
取得少走索引,取得多走全表扫描,这是正确选择;估计行数接近实际行数时不用担心。
比例 计划选了 计划的耗时 ms 强制全表扫描 ms 强制索引扫描 ms
0.01% Bitmap Heap Scan 0.04 39.57 0.02
1.00% Bitmap Heap Scan 4.37 37.65 3.41
10.00% Bitmap Heap Scan 20.37 39.58 40.09
30.00% Bitmap Heap Scan 46.40 46.66 125.51
50.00% Seq Scan 53.28 53.16 197.50
70.00% Seq Scan 56.77 56.35 279.67
90.00% Seq Scan 59.31 62.17 360.17随机分布的列上约 30% 起全表扫描更快,计划在 30%–50% 之间切换是对的。
where email = 'user777@Example.com':Index Scan using t_email on t (估计代价 8.44,估计行数 1) (实际行数 1);Execution Time 0.00 ms where lower(email) = 'user777@example.com'(只有普通索引):Seq Scan on t (估计代价 30424.00,估计行数 5000) (实际行数 1);Execution Time 89.28 ms 同一条语句(建了 lower(email) 的表达式索引之后):Index Scan using t_email_lower on t (估计代价 8.44,估计行数 1) (实际行数 1);Execution Time 0.00 ms where id + 1 = 500001(对列做算术):Seq Scan on t (估计代价 30424.00,估计行数 5000) (实际行数 1);Execution Time 32.63 ms where id = 500001 - 1(把运算挪到常量一侧):Index Scan using t_pkey on t (估计代价 8.44,估计行数 1) (实际行数 1);Execution Time 0.01 ms where id::text = '500000'(对列做类型转换):Seq Scan on t (估计代价 32924.00,估计行数 5000) (实际行数 1);Execution Time 58.10 ms
函数、算术、类型转换会让普通索引失效;建表达式索引,或把运算挪到常量一侧。
collation = C 普通索引:Index Only Scan using w_e on w → Index Cond: ((email >= 'user777'::text) AND (email < 'user778'::text)) collation = C 再加 text_pattern_ops 索引之后:Index Only Scan using w_e2 on w → Index Cond: ((email ~>=~ 'user777'::text) AND (email ~<~ 'user778'::text)) collation = en_US.UTF-8 普通索引:Seq Scan on w collation = en_US.UTF-8 再加 text_pattern_ops 索引之后:Index Only Scan using w_e2 on w → Index Cond: ((email ~>=~ 'user777'::text) AND (email ~<~ 'user778'::text))
C 排序规则下普通索引就行;其他排序规则要 text_pattern_ops 索引。
k < 300000 (约 30%),random_page_cost = 4 :计划 Bitmap Heap Scan ,耗时 42.73 ms k < 300000 (约 30%),random_page_cost = 1.1 :计划 Bitmap Heap Scan ,耗时 43.44 ms k < 500000 (约 50%),random_page_cost = 4 :计划 Seq Scan ,耗时 52.77 ms k < 500000 (约 50%),random_page_cost = 1.1 :计划 Index Scan ,耗时 190.70 ms k < 700000 (约 70%),random_page_cost = 4 :计划 Seq Scan ,耗时 54.91 ms k < 700000 (约 70%),random_page_cost = 1.1 :计划 Seq Scan ,耗时 55.80 ms
调低它会让计划更倾向索引扫描;数据在内存里时可能反而更慢。