我在 PostgreSQL 12 数据库中有一个包含数百万条记录的表,在从 11 升级到 12 后,一些查询开始表现得很糟糕。他们从大约 1 秒到大约 5 分钟。我尝试重建所有索引、清理和所有常见的 Postgres 低级成果,但性能仍然很糟糕。
这是查询:
SELECT id, activity_count
FROM user
WHERE (search_index) @@ (to_tsquery('pg_catalog.english', '''1234567890'':*') AND active = true
ORDER BY activity_count DESC LIMIT 101
换句话说,找到与给定帐号匹配的所有活跃用户,并从最活跃到最不活跃排序。
此查询仅返回 2 条记录大约需要 5 分钟。有什么不对劲。
该列search_index
是一个 tsvector,用于存储表的各种文本字段中的所有关键字(例如 account_number、name 等)。
我为此列创建了一个 GIN 索引,其中包含:
CREATE INDEX user_search_index_gin
ON public.user USING gin
(search_index)
TABLESPACE pg_default;
我还有一个active
列的索引:
CREATE INDEX user_active
ON public.user USING btree
(active ASC NULLS LAST)
TABLESPACE pg_default;
我有一个有序的索引activity_count
:
CREATE INDEX user_activity_count
ON public.user USING btree
(activity_count ASC NULLS LAST)
TABLESPACE pg_default;
然而,当我跑步时EXPLAIN
,我得到:
"Limit (cost=0.56..11443.66 rows=101 width=1552)"
" -> Index Scan Backward using user_activity_count on user (cost=0.56..36010185.91 rows=317836 width=1552)"
" Filter: (active AND (search_index @@ '''1234567890'':*'::tsquery))"
为什么只使用 user_activity_count 索引而不使用更高效的 GIN 索引?我该如何解决?
使用哪个索引?
虽然您的
WHERE
条件不是很有选择性,但当前的查询计划对ORDER BY activity_count DESC LIMIT 101
. 看:但是,您的谓词让我觉得很有选择性:
除非您的示例
'1234567890'
具有误导性。(您确定要在搜索词中使用单引号吗?)这显然不是您使用的实际查询。不匹配的括号。无论如何,您可能想要“简单”的文本搜索配置(加上一些改进):看:
统计数据
因此,您的统计数据似乎不是最新的。
您是否也将所有常见的 Postgres 低挂水果
ANALYZE
添加到您的篮子中?你当然没有提到。考虑手册中的说明:如果您的数据库很大,您可能会对该
vacuumdb
选项感兴趣--analyze-in-stages
。(那艘船可能已经为手头的案子航行了。)手册:有关的: