我有下表,包含 462541359 行。
create table "Prices"
(
"Id" bigint generated by default as identity
constraint "PK_Prices"
primary key,
"Timestamp" timestamp with time zone not null,
"DieselPrice" real not null,
"E5Price" real not null,
"E10Price" real not null,
"DieselChanged" boolean not null,
"E5Changed" boolean not null,
"E10Changed" boolean not null,
"StationId" uuid not null
constraint "FK_Prices_Stations_StationId"
references "Stations"
on delete cascade
);
alter table "Prices"
owner to postgres;
create index "IX_Prices_DieselChanged"
on "Prices" ("DieselChanged");
create index "IX_Prices_E10Changed"
on "Prices" ("E10Changed");
create index "IX_Prices_E5Changed"
on "Prices" ("E5Changed");
create index "IX_Prices_StationId"
on "Prices" ("StationId");
create index "IX_Prices_Timestamp"
on "Prices" ("Timestamp");
我将查询精简为这个(作为一个最小的例子)
select
count(*)
FROM "Prices"
where "StationId" = 'f38e56c1-e9ba-428f-adb0-bdefa428559b'
and "Timestamp" >= '2023-01-07'
其中StationId
只是 17000 个电台 ID 之一。
当我过滤此表时,初始运行的性能很差(大约 8 秒)。当我重新运行查询时,速度更快(大约 300 毫秒)。当我更改StationId
第一个查询时,它又变慢了。
我尝试使用来分析性能EXPLAIN (ANALYZE, BUFFERS)
并得到以下结果
Aggregate (cost=124159.66..124159.67 rows=1 width=8) (actual time=7734.619..7734.620 rows=1 loops=1)
Buffers: shared read=38398
-> Bitmap Heap Scan on ""Prices"" (cost=358.69..124141.54 rows=7246 width=0) (actual time=6668.499..7732.704 rows=9678 loops=1)
Recheck Cond: (""StationId"" = 'b07d169a-2856-4903-baee-d17e496ebfd0'::uuid)
Filter: (""Timestamp"" >= '2023-01-07 00:00:00+00'::timestamp with time zone)
Rows Removed by Filter: 28645
Heap Blocks: exact=38323
Buffers: shared read=38398
-> Bitmap Index Scan on ""IX_Prices_StationId"" (cost=0.00..356.88 rows=33107 width=0) (actual time=21.983..21.983 rows=38364 loops=1)"
Index Cond: (""StationId"" = 'b07d169a-2856-4903-baee-d17e496ebfd0'::uuid)
Buffers: shared read=34
Planning Time: 0.082 ms
JIT:
Functions: 7
Options: Inlining false, Optimization false, Expressions true, Deforming true
Timing: Generation 0.296 ms, Inlining 0.000 ms, Optimization 0.196 ms, Emission 2.469 ms, Total 2.961 ms
Execution Time: 7734.984 ms
从我读到的内容来看,Bitmap Heap Scan
仅部分使用了Timestamp
列上的索引,我怀疑这是性能低下的原因。
查询最初缓慢的原因是什么?如何更好地利用索引来加快按日期的过滤速度?当我将过滤器与更多过滤器结合使用时,如何确保保持索引的性能E5Changed
?
这是depesz 上格式良好的解释。
因此位图扫描使用 StationId 上的索引来标记 38364 行。这会读取几乎相同数量的缓冲区,这意味着数据可能是按时间戳顺序插入的,将具有任何单个 StationId 的行分布在整个表中,这通常是时间序列数据的情况。
如此大量的随机读取解释了查询速度慢的原因,尤其是在您不使用 SSD 的情况下。
那么这些行中有 75% 不满足时间戳条件,因此只保留 25% 的行。
现在,为什么它不使用时间戳索引?假设所有 StationId 的时间戳以相同的方式分布,这意味着您的时间戳条件将达到 25% 的行。如果它执行 BitmapAnd 来组合 Timestamp 和 StationId 上的索引,那么它必须扫描 StationId 索引和 Timestamp 索引中满足条件的所有索引行,并将两者组合在一个位图中。在 Timestamp 索引中,这将是 462541359 的 25%,或者大约 115M 索引行。Postgres 做出合理的假设,这不会是最快的选择,因此它选择另一个计划,这就是您所得到的。
更好的选择是 (StationId,Timestamp) 或 (Timestamp,StationId) 上的多列索引,它可以通过索引扫描直接满足条件。
但是...您应该选择哪一个呢?(a,b) 上的索引也是 (a) 上的索引,但不是 (b) 上的索引。因此,所选的多列索引将替换 StationId 或 Timestamp 上的现有索引之一。
(a,b) 上的索引允许对 (a) 和 (a,b) 进行范围搜索,但不能单独对 (b) 进行范围搜索。就像按(姓氏、名字)排序的电话簿一样,优化搜索,例如“姓氏是‘Smith’,名字以‘A’或‘B’开头”,因为所有满足条件的行都聚集为索引内的一个范围。
由于 StationId 是一个 uuid,因此您永远不会对其进行范围查询。但是您可能会使用“<”或 BETWEEN 组合“SiationId=constant”和“Timestamp in a range”进行查询。因此,在 (StationId,Timestamp) 上创建索引来优化这些索引更有意义。
出于安全原因,最好使用普通用户而不是 postgres。
非选择性指数永远不会被使用,因此它们是资源的浪费。
如果 bool 列的统计分布不是某个值的 99%、另一个值的 1%,则该索引是无用的。与可用于从表中数百万个时间戳中选择一个时间戳的时间戳索引不同,bool 只有两个可能的值...
如果您的表中只有百分之几的行的 bool 列设置为 true,那么这可能是足够有选择性的。但在这种情况下,索引中的绝大多数行的 bool 设置为 false,并且浪费了空间。在这种情况下,最好在 (bool_column) WHERE bool_column 上创建条件索引。所以索引只会存储值为“true”的行。(或者 WHERE 不是 bool_column,如果这样更有选择性的话)。但通常 bool 上的单列索引没有用。
即使 bool 列的选择性很强,如果您经常在查询中搜索这些列,那么在 (StationId,Timestamp) WHERE bool_column 上创建条件索引可能会更有效。这也可以用作普通索引,例如 (bool_column) WHERE bool_column。
如果您的表变得很大,您还可以按时间戳(例如按月)对其进行分区。然后,您可以在旧分区上使用 CLUSTER 按 (StationId,Timestamp) 顺序对磁盘上的行重新排序,这将使表中行的分布对问题中的查询更加友好。但是,由于行不再按时间戳排序,因此对仅使用时间戳而不是 StationId 条件的查询会产生相反的效果。
或者您可以使用专门研究时间序列的数据库,例如 clickhouse:
当然,这有另一组完全不同的妥协和用例。例如,它不执行更新,只执行插入。有几种专门研究时间序列的数据库,它们都有自己的怪癖和妥协。