我有下表,包含 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
?