Início / user-221305

derteufelqwe's questions

Martin Hope
derteufelqwe
Asked: 2024-01-09 18:07:53 +0800 CST
  • 5

Eu tenho a seguinte tabela com 462541359 linhas.

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");

Reduzi minha consulta a isso (como um exemplo mínimo)

select
    count(*)
FROM "Prices"
where "StationId" = 'f38e56c1-e9ba-428f-adb0-bdefa428559b'
  and "Timestamp" >= '2023-01-07'

onde StationIdestá apenas um dos 17.000 IDs de estação.

Quando filtro esta tabela, obtenho um desempenho ruim (aproximadamente 8s) na execução inicial. Quando executo a consulta novamente, ela fica muito mais rápida (aproximadamente 300 ms). Quando altero a StationIdprimeira consulta fica lenta novamente.

Tentei analisar o desempenho usando EXPLAIN (ANALYZE, BUFFERS)e obtive o seguinte resultado

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

Pelo que li, o Bitmap Heap Scaníndice da Timestampcoluna está sendo usado apenas parcialmente e suspeito que essa seja a causa do baixo desempenho.

Qual pode ser a causa da lentidão inicial da consulta e como posso utilizar melhor os índices para acelerar a filtragem por data? E como posso garantir que manterei o desempenho do índice ao combinar meu filtro com mais filtros como E5Changed?

postgresql