给出下表:
CREATE TABLE chat_message (
id bigint DEFAULT nextval('public.chat_message_id_seq'::regclass) NOT NULL,
"user" integer,
type smallint,
text text
);
ALTER TABLE ONLY chat_message ADD CONSTRAINT pk_chat_message PRIMARY KEY (id);
CREATE INDEX idx_chat_message_user_type ON chat_message USING btree ("user", type);
CREATE INDEX k_chat_message_user ON chat_message USING btree ("user");
其中类型为1
或NULL
,则查询:
EXPLAIN ANALYZE
SELECT *
FROM "chat_message" AS t
WHERE true
AND "type" = 1
AND "user" = 1234567
ORDER BY "user", "type", "id" ASC
LIMIT 10 OFFSET 0;
给出以下输出:
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=53644.94..53644.97 rows=10 width=127) (actual time=4.817..4.818 rows=6 loops=1)
-> Sort (cost=53644.94..53681.60 rows=14663 width=127) (actual time=4.816..4.816 rows=6 loops=1)
Sort Key: id
Sort Method: quicksort Memory: 26kB
-> Bitmap Heap Scan on chat_message t (cost=362.86..53328.08 rows=14663 width=127) (actual time=1.975..2.181 rows=6 loops=1)
Recheck Cond: (("user" = 1234567) AND (type = 1::smallint))
Heap Blocks: exact=3
-> Bitmap Index Scan on idx_chat_message_user_type (cost=0.00..359.19 rows=14663 width=0) (actual time=1.822..1.822 rows=6 loops=1)
Index Cond: (("user" = 1234567) AND (type = 1::smallint))
Planning time: 0.348 ms
Execution time: 5.028 ms
但是一旦 LIMIT 值降低到某个值以下(在我的本地机器上为 9),那么查询计划就会变为这样:
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.56..50193.33 rows=9 width=127) (actual time=23119.188..46005.965 rows=6 loops=1)
-> Index Scan using pk_chat_message on chat_message t (cost=0.56..81775168.50 rows=14663 width=127) (actual time=23119.187..46005.962 rows=6 loops=1)
Filter: ((type = 1::smallint) AND ("user" = 1234567))
Rows Removed by Filter: 49452956
Planning time: 14.840 ms
Execution time: 46006.683 ms
这实在是太慢了。
对于这个确切的用户来说,存在巨大的数据偏差:它有 50 000 行WHERE type is NULL
,但只有 6 行WHERE type = 1
。此外,请求相同的 LIMIT 9,但WHERE type is NULL
具有完全相同的查询计划,但运行速度很快:
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=153793.13..153793.15 rows=9 width=127) (actual time=886.897..886.898 rows=9 loops=1)
-> Sort (cost=153793.13..153909.07 rows=46374 width=127) (actual time=886.894..886.894 rows=9 loops=1)
Sort Key: gs_type, id
Sort Method: top-N heapsort Memory: 27kB
-> Bitmap Heap Scan on chat_message t (cost=1143.90..152826.25 rows=46374 width=127) (actual time=12.561..878.947 rows=49934 loops=1)
Recheck Cond: (("user" = 1234567) AND (type IS NULL))
Heap Blocks: exact=10903
-> Bitmap Index Scan on idx_chat_message_user_type (cost=0.00..1132.31 rows=46374 width=0) (actual time=9.942..9.942 rows=49934 loops=1)
Index Cond: (("user" = 1234567) AND (type IS NULL))
Planning time: 0.308 ms
Execution time: 887.027 ms
在生产服务器上,将完全相同的数据加载到规格与我的笔记本电脑不同的服务器(更多的内存、巨大的shared_buffers
、max_mem
来自不同其他表的恒定工作负载)中,其行为方式类似,只有限制阈值不同(最高到 75 时很慢Index Scan
,然后从 76 开始很快Bitmap Heap Scan
+Bitmap Index Scan
甚至更高)。
一些附加信息:
SELECT * FROM pg_stat_user_tables WHERE relname = 'chat_message';
relname |seq_scan |seq_tup_read |idx_scan |idx_tup_fetch |n_tup_ins |n_tup_upd |n_tup_del |n_tup_hot_upd |n_live_tup |n_dead_tup |n_mod_since_analyze|last_vacuum|last_autovacuum|last_analyze |last_autoanalyze |vacuum_count |autovacuum_count |analyze_count |autoanalyze_count |
chat_message|0 |0 |11 |197,652,914 |0 |0 |0 |0 |0 |0 |0 | | | | |0 |0 |0 |0 |
SELECT * FROM pg_stats where tablename = 'chat_message';
schemaname |tablename |attname |inherited|null_frac|avg_width|n_distinct|most_common_vals
public |chat_message |id |false |0 |8 |-1 |
public |chat_message |user |false |0 |4 |30145 |{redacted}
public |chat_message |text |false |0 |38 |45553 |{redacted}
public |chat_message |type |false |0.7656 |2 |1 |{1}
我的问题是:
Index Scan
为什么当涉及到那几行时,速度就会变得非常慢?- 为什么
Index Scan
总是使用pk_chat_message
索引,即使有更合适的索引idx_chat_message_user_type
,即使ORDER BY
子句包含WHERE
子句中的所有字段(order by 影响索引的使用)? - 为什么它
LIMIT N
会影响查询计划,因为它Index Scan
更喜欢Bitmap Index + Heap Scan
? - 怎样才能使这个查询对于这个
user + type
和其他查询表现良好(在 1 秒内)?
PostgreSQL 有两种选择来处理查询:
它可以对该子句使用索引
WHERE
,然后排序并返回前几个结果(这是你的快速计划)它可以对该子句使用索引
ORDER BY
,并丢弃不符合WHERE
条件的行,直到找到足够的结果行(这是您的慢速计划)决定哪个计划更好很难,PostgreSQL 有时肯定会出错。在你这个慢速情况下,它必须扫描 49452957 行,直到找到一个满足条件的行
WHERE
,即使估计有 14663 行(实际上是 49934 行)满足条件WHERE
。问题是 PostgreSQL 没有统计数据可以告诉它所有匹配的行都有一个很大的id
,所以它必须扫描很多行,直到找到一个。当然,如果您只需要很少的结果行,第二种(在您的情况下很慢)策略会变得更有吸引力,这解释了当您减少行数时优化器会切换到这样的计划
LIMIT
。注意,“索引扫描”与“位图索引扫描”的处理方式截然不同。前者将按索引顺序返回结果,而后者按表顺序返回行,但如果结果行很多,则性能会更好。
有两种方法可以改善这种情况:
创建一个支持
ORDER BY
和WHERE
条件的索引:使用一个粗暴的技巧来阻止 PostgreSQL 使用主键索引:
解决倾斜数据最快的方法是。对于您来说,像下面这样的索引将非常快速,而且非常紧凑。您可以通过更改或
partial index
来调整和测试索引。index column
filtering column