Início / dba / Perguntas / 319071
Accepted
Cerin
Asked: 2022-11-02 20:15:27 +0800 CST

Como acelerar a consulta de intervalo de datas de coluna única no Postgres?

  • 772

Como você aceleraria uma consulta do Postgres que está tentando filtrar em uma coluna de data entre uma data de início e término?

Estou executando uma consulta como:

SELECT * FROM record WHERE tag_id IN (1,2,3) AND person_id = 1 AND created >= '2022-1-1' AND created < '2022-6-1'
ORDER BY priority DESC LIMIT 100;

em uma tabela com milhões de linhas. No entanto, apenas alguns milhares de linhas devem ser aplicadas à minha consulta e tenho alguns índices que devem cobrir exatamente os critérios, como:

CREATE INDEX record_tag_priority_person_index
ON public.record USING btree
(tag_id ASC NULLS LAST, priority DESC NULLS LAST, person_id ASC NULLS LAST)
WHERE (tag_id = ANY (ARRAY[1, 2, 3])) AND person_id = 1;

CREATE INDEX record_created_index
ON public.record USING btree
(created ASC NULLS LAST);

No entanto, mesmo com esses índices, a consulta ainda leva cerca de 18 minutos para ser executada.

Se eu executar um EXPLAINna minha consulta, ele mostra:

"Limit  (cost=155990.12..155990.37 rows=100 width=165) (actual time=1104683.783..1104683.799 rows=100 loops=1)"
"  ->  Sort  (cost=155990.12..156078.05 rows=35170 width=165) (actual time=1104683.782..1104683.789 rows=100 loops=1)"
"        Sort Key: priority DESC"
"        Sort Method: top-N heapsort  Memory: 58kB"
"        ->  Bitmap Heap Scan on record  (cost=27359.52..154645.95 rows=35170 width=165) (actual time=556.641..1104569.771 rows=32804 loops=1)"
"              Recheck Cond: ((created >= '2022-01-01 04:00:00+00'::timestamp with time zone) AND (created < '2022-6-1 04:00:00+00'::timestamp with time zone) AND (tag_id = ANY ('{1,2,3}'::integer[])) AND (person_id = 1))"
"              Rows Removed by Index Recheck: 1103447"
"              Heap Blocks: exact=35800 lossy=99400"
"              ->  BitmapAnd  (cost=27359.47..27359.47 rows=35170 width=0) (actual time=547.819..547.821 rows=0 loops=1)"
"                    ->  Bitmap Index Scan on record_created_index  (cost=0.00..8666.93 rows=409449 width=0) (actual time=244.146..244.146 rows=309261 loops=1)"
"                          Index Cond: ((created >= '2022-01-01 04:00:00+00'::timestamp with time zone) AND (created < '2022-6-1 04:00:00+00'::timestamp with time zone))"
"                    ->  Bitmap Index Scan on record_tag_priority_person_index  (cost=0.00..18674.71 rows=2043655 width=0) (actual time=293.201..293.202 rows=2029783 loops=1)"
"Planning Time: 118.456 ms"
"Execution Time: 1104683.854 ms"

Portanto, está usando ambos os meus índices, mas ainda está demorando uma eternidade para encontrar os primeiros 100 resultados.

Como acelero isso? Meus índices são ineficientes?

Eu tentei combinar os dois índices em um índice parcial como:

CREATE INDEX record_tag_priority_person_created_index
ON public.record USING btree
(tag_id ASC NULLS LAST, priority DESC NULLS LAST, person_id ASC NULLS LAST, created DESC)
WHERE (tag_id = ANY (ARRAY[1, 2, 3])) AND person_id = 1;

mas o planejador não o pega e continua a usar os dois índices separados.

postgresql

2 respostas

  1. Best Answer

    Geralmente, filtrar por uma coluna, classificar por outra, combinado com um pequeno LIMITé um osso duro de roer.

    O tempo de execução ainda parece excessivo, mesmo para seus índices mal ajustados.

    Maiswork_mem

    Uma questão é revelada por isso:

    Blocos de pilha: exato = 35800 com perdas = 99400 "

    Ou seja, o Postgres não tinha o suficiente work_mempara armazenar identificadores de linha para páginas de dados identificadas. Sua consulta se beneficiaria muito com mais work_mem. Ver:

    Melhor índice

    Dependendo de quão seletivo esse filtro é:

    AND created >= '2022-1-1' AND created < '2022-6-1'

    Se não for muito seletivo, ou seja, uma grande porcentagem de linhas indexadas (das que passam pelo filtro de índice) se qualificar, isso deve servir bem:

    CREATE INDEX record_priority_part_idx ON public.record (priority DESC)
WHERE tag_id = ANY ('{1,2,3}'::int[]) AND person_id = 1;

    O Postgres pode percorrer o índice em ordem de classificação em uma varredura de índice simples e filtrar as (relativamente poucas) não correspondências até que a pequena LIMIT 100seja satisfeita.

    Usando priority DESCpara corresponder à sua consulta. Se você realmente quiser priority DESC NULLS LAST, use isso na consulta e no índice.

    Senão:

    CREATE INDEX record_created_part_idx ON public.record (created)
WHERE tag_id = ANY ('{1,2,3}'::int[]) AND person_id = 1;

    O ponto é ter a coluna de filtro restante createdcomo expressão de índice principal .

    Se poucas linhas corresponderem ao filtro, pode ser mais rápido executar uma verificação de índice e classificar as poucas linhas qualificadas. Este pequeno índice em apenas (created)será rápido para isso.

    Certifique-se de executar ANALYZEdepois de criar qualquer índice. O Postgres reunirá estatísticas para o índice parcial.

    Com o índice de ajuste mais apertado e estatísticas atualizadas, talvez você não precise aumentar work_mempara esta consulta.

    Com mais sofisticação você pode fazer ainda melhor. Especialmente se o timestamptzfiltro estiver no meio (nem muito seletivo, nem pouco seletivo). Ver:

    Aparte

    O plano de consulta também revela que createdé type timestamptz. Portanto, esta não é uma maneira segura de fornecer limites:

    E criado >= '2022-1-1' E criado < '2022-6-1'

    A configuração de fuso horário atual é assumida. Parece ser UTC no seu caso. Executar a mesma consulta com uma configuração de fuso horário diferente pode retornar resultados diferentes. Forneça timestamptzconstantes inequívocas (com deslocamento de tempo ou um fuso horário explícito) para ser seguro.

    • 3

  2. Com base no seu EXPLAIN PLAN, o bitmap deve ter encontrado pelo menos 35800+99400 linhas no índice, mas quando chegou à tabela havia apenas 32804 linhas visíveis atendendo aos critérios. A única maneira que consigo pensar para fazer isso acontecer nesse cenário é se seus índices estiverem cheios de linhas mortas. Tente aspirar a mesa para cuidar disso. Os índices Btree têm um recurso chamado "tuplas mortas" ou "microaspiração" onde usar o índice fará com que ele marque as tuplas mortas na tabela como também mortas no índice, mas as varreduras de bitmap não implementam esse recurso (mas se beneficiam de outras consultas tendo feito isso). (Além disso, os hot-standbys ignoram essas marcações e, portanto, não podem se beneficiar desse recurso.) Se seus índices forem usados ​​apenas para varreduras de bitmap,

    Seu índice parcial não funcionará da maneira que você aparentemente pensa que funcionará. Primeiro, sua decoração de ordenação em "prioridade" está errada, você a define DESC NULLS LAST, mas sua consulta é DESC NULLS FIRST(os NULLS FIRST são entendidos como implícitos para DESC). O planejador poderia fazer um trabalho melhor ao lidar com essas incompatibilidades, mas isso não acontece. Ele simplesmente não usará essa parte desse índice para ordenação.

    Mesmo se não fosse por essa incompatibilidade, ele ainda não o usaria para ordenação, porque uma lista IN em uma coluna anterior faz com que não use uma coluna seguinte para ordenação. (A exceção é se o planejador perceber que a lista IN só pode ter um valor e, portanto, converter em igualdade simples). Novamente, é plausível que o PostgreSQL possa fazer um trabalho melhor aqui (usando algo como múltiplas varreduras de índice com um "merge append" entre eles), mas ninguém implementou isso. Como todo o benefício de tag_id já foi obtido na cláusula WHERE, há apenas uma desvantagem em incluir essa coluna no índice (para essa consulta específica)

    E mesmo que não fosse por essas duas coisas, ele ainda pode não usá-lo para ordenação, pois as varreduras de bitmap inerentemente não preservam a ordem. Então ele tem que escolher, usar "prioridade" para ordenar com uma varredura btree comum e precisa filtrar em "criado", ou usar o índice "criado" e renunciar a ordenar em "prioridade".

    Finalmente, seu hardware não parece muito bom. Parece que você está recebendo cerca de 8ms por buffer (assumindo que nenhum deles estava em cache), que é o que você esperaria de um único disco rígido de baixo custo. Armazenamento mais rápido ou mais RAM para armazenar em cache as páginas do disco na memória podem oferecer grandes melhorias.

    • 1

relate perguntas