我在PostgreSQL 12.13中有以下基本表：
Record:
database_id: FK to database table
updated_at: timestamp我已经在database_id和updated_at字段上创建了索引。
我有一个查询，它获取给定数据库ID的最近100条记录：
SELECT * FROM record WHERE database_id='fa9bcfa6-8d89-4c95-b04a-24c85b169066'
ORDER BY store_record.updated_at DESC
LIMIT 100;此查询非常慢（最近运行大约需要6分钟）。下面是查询计划：
Limit (cost=0.09..1033.82 rows=100 width=486)
-> Index Scan Backward using record_updated_at on record (cost=0.09..8149369.05 rows=788343 width=486)
Filter: (database_id = 'fa9bcfa6-8d89-4c95-b04a-24c85b169066'::uuid)如果我将ORDER BY DESC更改为ORDER BY ASC，则查询将花费毫秒，即使查询计划看起来大致相同：
SELECT * FROM record WHERE database_id='fa9bcfa6-8d89-4c95-b04a-24c85b169066'
ORDER BY store_record.updated_at
LIMIT 100;
Limit (cost=0.09..1033.86 rows=100 width=486)
-> Index Scan using record_updated_at on record (cost=0.09..8149892.78 rows=788361 width=486)
Filter: (database_id = 'fa9bcfa6-8d89-4c95-b04a-24c85b169066'::uuid)如果我完全删除ORDER BY，那么查询也会很快：
SELECT * FROM record WHERE database_id='fa9bcfa6-8d89-4c95-b04a-24c85b169066'
LIMIT 100;
Limit (cost=0.11..164.75 rows=100 width=486)
-> Index Scan using record_database_id on record (cost=0.11..1297917.10 rows=788366 width=486)
Index Cond: (database_id = 'fa9bcfa6-8d89-4c95-b04a-24c85b169066'::uuid)几个问题：
- 为什么第一个查询比其他两个查询慢这么多？我理解为什么最后一个查询更快，但我不明白为什么将
ORDER BY DESC更改为ORDER BY会产生如此大的差异。 - 如何加快初始查询？
1条答案
按热度按时间eyh26e7m1#
该计划按照update_at DESC的顺序跟随索引读取记录，测试每个记录是否具有所需的database_id，然后在找到100个具有所需database_id的记录时立即停止。但是，特定的所需database_id值在索引的一端比在另一端更常见。这里的DESC没有什么神奇之处。假设有一些其他的数据库ID值，它的工作方式与此相反，在下降时找到100个值比在上升时快得多。（分析），根据“筛选器删除的行”的不同报告值，这将立即清除
如果在
(database_id,updated_at)上有一个多列索引，那么它可以立即跳转到索引的所需位置，并读取它找到的前100行（在“过滤”它们以获得可见性之后），无论您希望ORDER BY向哪个方向移动，这都将快速工作。