postgresql - Postgres 不使用索引对数据进行排序
问题描述
我有一张learners
大约有 320 万行的表格。此表包含用户相关信息,例如姓名和电子邮件。我需要优化一些在某些列上使用 order by 的查询。因此,为了进行测试,我创建了一个temp_learners
包含 80 万行的表。我在这个表上创建了两个索引:
CREATE UNIQUE INDEX "temp_learners_companyId_userId_idx"
ON temp_learners ("companyId" ASC, "userId" ASC, "learnerUserName" ASC, "learnerEmailId" ASC);
和
CREATE INDEX temp_learners_company_name_email_index
ON temp_learners ("companyId", "learnerUserName", "learnerEmailId");
第二个索引仅用于测试。现在当我运行这个查询时:
SELECT *
FROM temp_learners
WHERE "companyId" = 909666665757230431 AND "userId" IN (
4990609084216745771,
4990610022492247987,
4990609742667096366,
4990609476136523663,
5451985767018841230,
5451985767078553638,
5270390122102920730,
4763688819142650938,
5056979692501246449,
5279569274741647114,
5031660827132289520,
4862889373349389098,
5299864070077160421,
4740222596778406913,
5320170488686569878,
5270367618320474818,
5320170488587895729,
5228888485293847415,
4778050469432720821,
5270392314970177842,
4849087862439244546,
5270392117430427860,
5270351184072717902,
5330263074228870897,
4763688829301614114,
4763684609695916489,
5270390232949727716
) ORDER BY "learnerUserName","learnerEmailId";
db 使用的查询计划是这样的:
Sort (cost=138.75..138.76 rows=4 width=1581) (actual time=0.169..0.171 rows=27 loops=1)
" Sort Key: ""learnerUserName"", ""learnerEmailId"""
Sort Method: quicksort Memory: 73kB
-> Index Scan using "temp_learners_companyId_userId_idx" on temp_learners (cost=0.55..138.71 rows=4 width=1581) (actual time=0.018..0.112 rows=27 loops=1)
" Index Cond: ((""companyId"" = '909666665757230431'::bigint) AND (""userId"" = ANY ('{4990609084216745771,4990610022492247987,4990609742667096366,4990609476136523663,5451985767018841230,5451985767078553638,5270390122102920730,4763688819142650938,5056979692501246449,5279569274741647114,5031660827132289520,4862889373349389098,5299864070077160421,4740222596778406913,5320170488686569878,5270367618320474818,5320170488587895729,5228888485293847415,4778050469432720821,5270392314970177842,4849087862439244546,5270392117430427860,5270351184072717902,5330263074228870897,4763688829301614114,4763684609695916489,5270390232949727716}'::bigint[])))"
Planning time: 0.116 ms
Execution time: 0.191 ms
在这种情况下,它不对索引进行排序。但是当我运行这个查询时
SELECT *
FROM temp_learners
WHERE "companyId" = 909666665757230431
ORDER BY "learnerUserName","learnerEmailId" limit 500;
此查询在排序时使用索引。
Limit (cost=0.42..1360.05 rows=500 width=1581) (actual time=0.018..0.477 rows=500 loops=1)
-> Index Scan using temp_learners_company_name_email_index on temp_learners (cost=0.42..332639.30 rows=122327 width=1581) (actual time=0.018..0.442 rows=500 loops=1)
Index Cond: ("companyId" = '909666665757230431'::bigint)
Planning time: 0.093 ms
Execution time: 0.513 ms
我无法理解的是为什么 postgre 在第一次查询中不使用索引?另外,我想澄清一下,这个表的正常用例learner
是与其他表连接。所以我写的第一个查询更类似于连接方程。例如,
SELECT *
FROM temp_learners AS l
INNER JOIN entity_learners_basic AS elb
ON l."companyId" = elb."companyId" AND l."userId" = elb."userId"
WHERE l."companyId" = 909666665757230431 AND elb."gameId" = 1050403501267716928
ORDER BY "learnerUserName", "learnerEmailId" limit 5000;
即使在更正索引之后,查询计划也不会为排序编制索引。
QUERY PLAN
Limit (cost=3785.11..3785.22 rows=44 width=1767) (actual time=163.554..173.135 rows=5000 loops=1)
-> Sort (cost=3785.11..3785.22 rows=44 width=1767) (actual time=163.553..172.791 rows=5000 loops=1)
" Sort Key: l.""learnerUserName"", l.""learnerEmailId"""
Sort Method: external merge Disk: 35416kB
-> Nested Loop (cost=1.12..3783.91 rows=44 width=1767) (actual time=0.019..63.743 rows=21195 loops=1)
-> Index Scan using primary_index__entity_learners_basic on entity_learners_basic elb (cost=0.57..1109.79 rows=314 width=186) (actual time=0.010..6.221 rows=21195 loops=1)
Index Cond: (("companyId" = '909666665757230431'::bigint) AND ("gameId" = '1050403501267716928'::bigint))
-> Index Scan using "temp_learners_companyId_userId_idx" on temp_learners l (cost=0.55..8.51 rows=1 width=1581) (actual time=0.002..0.002 rows=1 loops=21195)
Index Cond: (("companyId" = '909666665757230431'::bigint) AND ("userId" = elb."userId"))
Planning time: 0.309 ms
Execution time: 178.422 ms
Postgres 在连接和排序数据时不使用索引吗?
解决方案
PostgreSQL 选择它认为会更快的计划。使用以正确顺序提供行的索引意味着使用选择性低得多的索引,因此它认为总体上不会更快。
如果你想强迫 PostgreSQL 相信排序是世界上最糟糕的事情,你可以set enable_sort=off
. 如果它在那之后仍然排序,那么你知道 PostgreSQL 没有正确的索引来避免排序,而不是仅仅认为它们实际上不会更快。
推荐阅读
- javascript - 如何通过单击将图像下载到客户端存储?
- php - Symfony 5 测试用例
- arrays - How can I read in characters with getchar() without the newline?
- object - 如何使两个对象之间的碰撞结合起来创建一个新对象?另外,我怎样才能将新对象发送到一个小时后被调用?
- reactjs - 从 React 16 升级到 17 后 React 接口错误“解析错误:意外令牌”
- javascript - 函数不会返回文件
- annotations - 如何为 U-net 构建二进制掩码?
- class - 有什么方法可以用类而不是 data-fancybox 属性来触发fancybox 3?
- python - 如何将活动的 tkinter 画布保存为 png 文件?
- twitter-bootstrap - bootstrap 不会生效,因为提供的路径是错误的