您将如何加快试图过滤开始日期和结束日期之间的日期列的 Postgres 查询?
我正在运行如下查询:
SELECT * FROM record WHERE tag_id IN (1,2,3) AND person_id = 1 AND created >= '2022-1-1' AND created < '2022-6-1'
ORDER BY priority DESC LIMIT 100;
在具有数百万行的表上。但是,只有几千行应该应用于我的查询,并且我有几个索引应该完全涵盖标准,例如:
CREATE INDEX record_tag_priority_person_index
ON public.record USING btree
(tag_id ASC NULLS LAST, priority DESC NULLS LAST, person_id ASC NULLS LAST)
WHERE (tag_id = ANY (ARRAY[1, 2, 3])) AND person_id = 1;
CREATE INDEX record_created_index
ON public.record USING btree
(created ASC NULLS LAST);
然而,即使有了这些索引,查询仍然需要大约 18 分钟才能运行。
如果我EXPLAIN
在查询中运行,它会显示:
"Limit (cost=155990.12..155990.37 rows=100 width=165) (actual time=1104683.783..1104683.799 rows=100 loops=1)"
" -> Sort (cost=155990.12..156078.05 rows=35170 width=165) (actual time=1104683.782..1104683.789 rows=100 loops=1)"
" Sort Key: priority DESC"
" Sort Method: top-N heapsort Memory: 58kB"
" -> Bitmap Heap Scan on record (cost=27359.52..154645.95 rows=35170 width=165) (actual time=556.641..1104569.771 rows=32804 loops=1)"
" Recheck Cond: ((created >= '2022-01-01 04:00:00+00'::timestamp with time zone) AND (created < '2022-6-1 04:00:00+00'::timestamp with time zone) AND (tag_id = ANY ('{1,2,3}'::integer[])) AND (person_id = 1))"
" Rows Removed by Index Recheck: 1103447"
" Heap Blocks: exact=35800 lossy=99400"
" -> BitmapAnd (cost=27359.47..27359.47 rows=35170 width=0) (actual time=547.819..547.821 rows=0 loops=1)"
" -> Bitmap Index Scan on record_created_index (cost=0.00..8666.93 rows=409449 width=0) (actual time=244.146..244.146 rows=309261 loops=1)"
" Index Cond: ((created >= '2022-01-01 04:00:00+00'::timestamp with time zone) AND (created < '2022-6-1 04:00:00+00'::timestamp with time zone))"
" -> Bitmap Index Scan on record_tag_priority_person_index (cost=0.00..18674.71 rows=2043655 width=0) (actual time=293.201..293.202 rows=2029783 loops=1)"
"Planning Time: 118.456 ms"
"Execution Time: 1104683.854 ms"
所以它使用了我的两个索引,但仍然需要很长时间才能找到前 100 个结果。
我该如何加快速度?我的索引效率低吗?
我尝试将这两个索引组合成一个部分索引,例如:
CREATE INDEX record_tag_priority_person_created_index
ON public.record USING btree
(tag_id ASC NULLS LAST, priority DESC NULLS LAST, person_id ASC NULLS LAST, created DESC)
WHERE (tag_id = ANY (ARRAY[1, 2, 3])) AND person_id = 1;
但计划者没有选择它并继续使用两个单独的索引。
通常,按一列过滤,按另一列排序,再加上一个小问题,这
LIMIT
是一个难以破解的难题。即使对于不合适的索引,运行时间似乎仍然过长。
更多的
work_mem
由此揭示了一个问题:
这意味着,Postgres 没有足够的空间
work_mem
来存储已识别数据页的行标识符。您的查询将从 more 中受益匪浅work_mem
。看:更好的索引
根据此过滤器的选择性:
如果它不是很有选择性,即很大比例的索引行(通过索引过滤器的那些)符合条件,那么这应该很好:
Postgres 可以在普通索引扫描中按排序顺序遍历索引,并过滤(相对较少的)不匹配项,直到
LIMIT 100
满足小项。用于
priority DESC
匹配您的查询。如果您真的想priority DESC NULLS LAST
在查询和索引中使用它。别的:
关键是将剩余的过滤列
created
作为前导索引表达式。如果只有很少的行与过滤器匹配,则运行索引扫描可能会更快,然后对少数符合条件的行进行排序。这个小索引
(created)
会很快。确保
ANALYZE
在创建任一索引后运行。Postgres 将收集部分索引的统计信息。使用更紧密的拟合索引和更新的统计信息,您可能不必
work_mem
为此查询增加。有了更多的复杂性,您可以做得更好。特别是如果
timestamptz
过滤器介于两者之间(既不是非常有选择性,也不是几乎没有选择性)。看:在旁边
查询计划还显示
created
typetimestamptz
。所以这不是提供界限的安全方法:假定当前时区设置。在你的情况下似乎是UTC。使用不同的时区设置运行相同的查询可能会返回不同的结果。提供明确
timestamptz
的常量(带有时间偏移或明确的时区)以确保安全。根据您的解释计划,位图必须在索引中找到至少 35800+99400 行,但是当它到达表时,只有 32804 可见行符合条件。我能想到在这种情况下发生这种情况的唯一方法是,如果您的索引因死行而膨胀。尝试用吸尘器吸尘以解决这个问题。Btree 索引有一个称为“killed tuples”或“microvacuuming”的功能,其中使用索引会导致它将表中的死元组标记为索引中的死元组,但位图扫描不实现此功能(但确实受益于其他查询已经完成了)。(此外,热备份会忽略这些标记,因此根本无法从此功能中受益。)如果您的索引仅用于位图扫描,
您的部分索引不会按照您显然认为的方式工作。首先,您对“优先级”的排序装饰是错误的,您定义了它
DESC NULLS LAST
,但您的查询是DESC NULLS FIRST
(NULLS FIRST 被理解为 DESC 的隐含)。计划者似乎可以更好地处理这种不匹配,但事实并非如此。它只是不会使用该索引的那一部分进行排序。即使不是因为不匹配,它仍然不会使用它进行排序,因为前一列上的 IN 列表使其不使用以下列进行排序。(例外情况是如果规划器意识到 IN 列表只能有一个值,因此转换为简单相等)。同样,PostgreSQL 在这里可以做得更好是有道理的(使用诸如多个索引扫描之类的东西,它们之间有一个“合并附加”),但没有人实现这一点。由于 tag_id 的全部好处已经在 WHERE 子句中得到,因此在索引中包含该列(对于此特定查询)只有缺点
即使不是因为这两件事,它仍然可能不会将其用于排序,因为位图扫描本质上不会保持顺序。所以它必须选择,使用“priority”进行普通btree扫描的排序并需要过滤“created”,或者使用“created”索引并放弃“priority”排序。
最后,您的硬件似乎不是很好。看起来每个缓冲区大约 8 毫秒(假设它们都没有在缓存中),这是您对单个低端硬盘的期望。更快的存储,或者更多的 RAM 用于在内存中缓存磁盘页面,都可以给你带来很大的改进。