我shared_buffers
在 Mac 上运行 PostgreSQL 11,并将内存设置为 3 GB。我有一个job
包含 500 万行的表。表结构是
Table "public.job"
Column | Type | Collation | Nullable | Default
------------+--------------------------+-----------+----------+---------
id | uuid | | not null |
name | text | | |
created_on | timestamp with time zone | | |
updated_on | timestamp with time zone | | |
Indexes:
"job_pkey" PRIMARY KEY, btree (id)
"job_created_on_idx" btree (created_on)
"job_name_idx" btree (name)
"job_updated_on_idx" btree (updated_on)
"job_updated_on_name_compound_asc_idx" btree (updated_on, upper(name))
"job_updated_on_name_compound_desc_idx" btree (updated_on DESC, upper(name))
注意我已经在updated_on
和name
列上创建了复合索引。
当我运行查询时select name, created_on from job where created_on >= '2023-10-08 00:00:00+08'::timestamp with time zone AND created_on < '2023-10-16 00:00:00+08' ORDER BY updated_on ASC, UPPER(name::text) ASC limit 25
,PostgreSQL 使用复合索引job_updated_on_name_compound_asc_idx
,花费了超过 4 秒的时间。
执行计划
Limit (cost=0.43..102.29 rows=25 width=61) (actual time=4549.668..4550.235 rows=25 loops=1)
Buffers: shared hit=4859940
-> Index Scan using job_updated_on_name_compound_asc_idx on job (cost=0.43..416764.16 rows=102293 width=61) (actual time=4549.667..4550.230 rows=25 loops=1)
Filter: ((created_on >= '2023-10-08 00:00:00+08'::timestamp with time zone) AND (created_on < '2023-10-16 00:00:00+08'::timestamp with time zone))
Rows Removed by Filter: 4828894
Buffers: shared hit=4859940
Planning Time: 0.218 ms
Execution Time: 4550.260 ms
该列有索引created_on
,但未使用。created_on
我可以通过附加id
到order by子句来强制 PostgreSQL 使用列索引。查询是select name, created_on from job where created_on >= '2023-10-08 00:00:00+08'::timestamp with time zone AND created_on < '2023-10-16 00:00:00+08' ORDER BY updated_on ASC, UPPER(name::text) ASC, id limit 25;
. 这次,PostgreSQL 使用了列上的索引created_on
,并且非常快地返回结果。
执行计划
Limit (cost=52190.61..52193.52 rows=25 width=77) (actual time=125.192..138.055 rows=25 loops=1)
Buffers: shared hit=42788
-> Gather Merge (cost=52190.61..62136.44 rows=85244 width=77) (actual time=125.191..138.049 rows=25 loops=1)
Workers Planned: 2
Workers Launched: 2
Buffers: shared hit=42788
-> Sort (cost=51190.58..51297.14 rows=42622 width=77) (actual time=119.359..119.362 rows=20 loops=3)
Sort Key: updated_on, (upper(name)), id
Sort Method: top-N heapsort Memory: 30kB
Worker 0: Sort Method: top-N heapsort Memory: 31kB
Worker 1: Sort Method: top-N heapsort Memory: 31kB
Buffers: shared hit=42788
-> Parallel Bitmap Heap Scan on job (cost=2512.94..49987.82 rows=42622 width=77) (actual time=19.915..109.984 rows=36562 loops=3)
Recheck Cond: ((created_on >= '2023-10-08 00:00:00+08'::timestamp with time zone) AND (created_on < '2023-10-16 00:00:00+08'::timestamp with time zone))
Heap Blocks: exact=24557
Buffers: shared hit=42738
-> Bitmap Index Scan on job_created_on_idx (cost=0.00..2487.36 rows=102293 width=0) (actual time=16.909..16.909 rows=109685 loops=1)
Index Cond: ((created_on >= '2023-10-08 00:00:00+08'::timestamp with time zone) AND (created_on < '2023-10-16 00:00:00+08'::timestamp with time zone))
Buffers: shared hit=395
Planning Time: 0.168 ms
Execution Time: 138.115 ms
如果数据库忙于更新大列行,则执行时间的差异会变得更大。
复合索引是为了提高排序性能而创建的,在某些情况下非常有用。由于我的系统根据用户选择动态生成 SQL,因此查询条件和排序可能会有所不同。在这种特定情况下,添加id
到order by子句以避免使用复合索引可以提高性能,但也许在其他一些情况下使用复合索引更好,所以我不能只是简单地删除复合索引。
我还检查了pg_stats表,结果如下:
attname | inherited | n_distinct | most_common_vals
------------+-----------+------------+------------------
id | f | -1 |
name | f | -1 |
created_on | f | -0.908167 |
updated_on | f | -1 |
我有两个问题:
- 对于上面的查询,显然使用索引
created_on
更好。为什么PostgreSQL选择order by子句的复合索引?我可以在 PostgreSQL 上配置什么让它使用正确的索引吗? - 看起来 PostgreSQL 不会在查询条件和order by中同时使用列索引。
Filter
尽管 中使用的列已建立索引,但它位于Filter
复合索引下。PostgreSQL 是否可以在单个查询中同时使用order by的复合索引和查询条件列的索引?
看来created_on 和updated_on 列彼此高度相关。但 PostgreSQL 没有机制可以了解这一点。它隐含地假设它们是不相关的。在任何已发布或正在开发的 PostgreSQL 版本中,您都无法对这一假设采取任何措施。
它假设需要过滤掉 500 万行中的大约 25/102293 行,或者大约 1200 行,然后才能停止索引扫描。但由于索引扫描的整个早期部分被created_on过滤条件丢弃(代价很大),它实际上必须在找到要保留的25行之前过滤掉4859940行。因此,估计值相差约 4000 倍。
如果您的列遵循其名称所暗示的直观语义,则在创建行之前无法更新行,因此created_on>='2023-10-08 00:00:00+08'条件也意味着updated_on>='2023- 10-08 00:00:00+08'。如果您手动提供此推断条件,那么扫描将跳过索引的整个早期部分,并且在我手中变得非常快。规划器不会为您提供此推断,即使您有理论上允许它这样做的 CHECK 约束,但也许您可以更改您的应用程序以自动为您生成该推断。
基于您的第一个计划中“由过滤器删除的行”大约等于“缓冲区:共享命中”这一事实,很明显表中行的物理顺序与时间戳列没有很强的相关性。因此,通过按索引顺序跟踪表,它必须跳过整个表。即使你的shared_buffers足够大,所有东西都在内存中,但这仍然很慢。部分原因可能是取消固定和重新固定缓冲区是一项昂贵的操作,部分原因可能是这样做会破坏 CPU 缓存,而主内存比 CPU 缓存慢得多。验证这一点,如果我使用 job_updated_on_name_compound_asc_idx 对表进行集群,查询在我手中的速度会提高大约 10 倍。或者,如果我只是将created_on添加到索引中以使其
(updated_on, upper(name), created_on)
然后它只需使用索引就可以过滤出created_on 值,而无需访问表,这也使其速度更快。后者可能是最好的选择,因为索引将自行维护,并且可以与其他操作同时创建,这两者都不适用于 CLUSTER。请注意,此技巧在 v13 中不再起作用,其中添加了增量排序。此时,它会很乐意使用索引进行主要排序,然后使用增量排序仅对关系进行重新排序以获得整体顺序。如果您想手动强制不使用索引,更安全的方法是使 ORDER BY 中的第一列成为与索引不匹配的虚拟表达式:
PostgreSQL 的某些未来版本可能会变得足够聪明,能够识破这个伎俩,因此仍然使用“错误”的索引,但当前或开发中的版本还没有。
为了直接解决你的第二个问题,不,它不会以这种方式组合索引,一个进行过滤,另一个进行排序。用于组合索引的代码是位图代码,它会丢失任何顺序。应该可以添加一个节点类型,该节点类型执行常规索引扫描(维护顺序),但附加一个用于过滤的填充位图。我认为它只需要编程(即无需更改数据在磁盘上的表示形式),但仍然需要大量工作,而且没有人这样做。我曾经想过几次,但没有做出具体的尝试。这也将是一种相当创新的节点,我怀疑因此很难将其接受到代码库中。还,在您的情况下,它可能不会比仅将“过滤”列作为最后一列添加到现有排序索引的定义中更有效。(我认为这段代码的真正用途是当附加的位图本身就是组合多个索引的结果时,这对你来说似乎不是这种情况)