Postgres filtra coluna timestamptz por data muito lenta (possivelmente índice não usado?)

Aqui está a explicação com uma boa formatação em depesz .

Portanto, a varredura de bitmap usa o índice em StationId para sinalizar 38.364 linhas. Isso lê quase o mesmo número de buffers, o que significa que os dados provavelmente foram inseridos na ordem do carimbo de data/hora, espalhando linhas com qualquer StationId individual por toda a tabela, o que geralmente é o caso com dados de séries temporais.

Esse grande número de leituras aleatórias explica por que a consulta é lenta, principalmente se você não estiver usando SSDs.

Então, 75% dessas linhas não satisfazem a condição de carimbo de data/hora, portanto, apenas 25% das linhas são mantidas.

Agora, por que não usa o índice no carimbo de data/hora? Supondo que os carimbos de data/hora sejam distribuídos da mesma maneira para todos os StationIds, isso significa que sua condição de carimbo de data/hora atingiria 25% das linhas. Se fizesse um BitmapAnd para combinar índices em Timestamp e StationId, então teria que varrer todas as linhas do índice que satisfazem a condição no índice StationId e no índice Timestamp e combinar os dois em um bitmap. No índice Timestamp, isso seria 25% de 462541359, ou cerca de 115 milhões de linhas do índice. O Postgres faz uma suposição razoável de que essa não será a opção mais rápida, por isso escolhe outro plano, que é o que você obteve.

Uma opção muito melhor seria um índice de múltiplas colunas em (StationId,Timestamp) ou (Timestamp,StationId), que satisfaria as condições diretamente com uma varredura de índice.

Mas... qual você deve escolher? Um índice em (a,b) também é um índice em (a), mas não um índice em (b). Assim, o índice multicoluna escolhido substituirá um dos índices existentes, seja em StationId ou Timestamp.

Um índice em (a,b) permite pesquisas de intervalo em (a) e (a,b), mas não apenas em (b). Assim como uma lista telefônica classificada por (sobrenome, nome) otimiza pesquisas como "o sobrenome é 'Smith' e o primeiro_nome começa com 'A' ou 'B'" porque todas as linhas que satisfazem são agrupadas como um intervalo dentro do índice .

Como StationId é um uuid, você nunca fará consultas de intervalo nele. Mas provavelmente você fará consultas combinando "SiationId=constant" e "Timestamp in a range", usando "<" ou BETWEEN. Portanto, faz muito mais sentido criar um índice em (StationId,Timestamp) que irá otimizá-los.

alter table "Prices" owner to postgres;

É melhor usar um usuário normal em vez do postgres, por motivos de segurança.

create index "IX_Prices_DieselChanged" on "Prices" ("DieselChanged");
create index "IX_Prices_E10Changed" on "Prices" ("E10Changed");
create index "IX_Prices_E5Changed" on "Prices" ("E5Changed");

Índices não seletivos nunca serão utilizados, portanto são um desperdício de recursos.

Se a distribuição estatística da coluna bool não for algo como 99% para um valor e 1% para o outro, o índice é inútil. Ao contrário de um índice no carimbo de data/hora que pode ser usado para escolher um carimbo de data/hora entre milhões na tabela, bool tem apenas dois valores possíveis...

Se sua tabela tiver apenas uma pequena porcentagem de linhas com a coluna bool definida como verdadeira, isso poderá ser seletivo o suficiente. Mas, neste caso, a grande maioria das linhas do índice tem o bool definido como falso e está desperdiçando espaço. Neste caso é melhor criar um índice condicional em (bool_column) WHERE bool_column. Portanto, o índice armazenará apenas as linhas com o valor “true”. (Ou WHERE não bool_column, se for mais seletivo). Mas geralmente índices de coluna única em bool não são úteis.

Mesmo que a coluna bool seja muito seletiva, provavelmente seria mais eficiente criar um índice condicional em (StationId,Timestamp) WHERE bool_column se você os pesquisar com frequência em suas consultas. Isso também funcionará como um índice simples como (bool_column) WHERE bool_column.

Se sua tabela ficar enorme, você também poderá particioná-la por carimbo de data/hora (digamos, por mês). Então, você pode usar CLUSTER em partições antigas para reordenar as linhas no disco na ordem (StationId, Timestamp), o que tornará a distribuição das linhas na tabela muito mais amigável para a consulta na questão. No entanto, como as linhas não são mais classificadas por carimbo de data/hora, isso terá o efeito oposto em consultas usando apenas condições de carimbo de data/hora e não de StationId.

Ou você pode usar um banco de dados especializado em séries temporais, como o clickhouse:

select topic, count(*) from mqtt_float group by topic order by 2 desc limit 3;

┌─topic──────────────────────────────────────┬───count()─┐
│ pv/meter/total_power                       │ 232506715 │
│ pv/meter/phase_1_current                   │  50604345 │
│ pv/meter/phase_2_current                   │  49684640 │
└────────────────────────────────────────────┴───────────┘

5 rows in set. Elapsed: 0.979 sec. Processed 1.58 billion rows, 1.58 GB (1.61 billion rows/s., 1.61 GB/s.)

select count(*) from mqtt_float where topic='pv/meter/phase_1_current' and ts > '2023-10-01';

┌──count()─┐
│ 13054580 │
└──────────┘

1 row in set. Elapsed: 0.019 sec. Processed 13.12 million rows, 118.07 MB (685.46 million rows/s., 6.17 GB/s.)

Isso tem outro conjunto completamente diferente de compromissos e casos de uso, é claro. Por exemplo, não faz atualizações, apenas inserções. Existem vários bancos de dados especializados em séries temporais, todos com suas peculiaridades e conjuntos de compromissos.