Início / dba / Perguntas / 339777
Accepted
Razgriz
Asked: 2024-05-24 19:41:44 +0800 CST

Um valor de filtro diferente resulta em um plano de consulta diferente (mais lento)

  • 772

Estou executando a seguinte consulta no Postgres 15 com a extensão Timescale em uma tabela de alertas para obter o alerta mais recente para um nome de usuário.

EXPLAIN ANALYZE
SELECT *
FROM alerts_alerts
WHERE username IN ('<username_here>')
ORDER BY timestamp DESC
LIMIT 1

Para a maioria dos nomes de usuário, a consulta é executada rapidamente, em menos de 150 ms. No entanto, para alguns nomes de usuário, leva mais tempo. Quase todas as bases de dados têm aproximadamente o mesmo número de alertas, cerca de 450, e a maioria delas tem dados bastante recentes, todos dos últimos 6 meses.

Aqui está o Explain Analyzenome de usuário problemático:

"Limit  (cost=0.29..2262.68 rows=1 width=86) (actual time=36129.346..36129.370 rows=1 loops=1)"
"  ->  Custom Scan (ChunkAppend) on alerts_alerts  (cost=0.29..2262.68 rows=1 width=86) (actual time=36129.344..36129.368 rows=1 loops=1)"
"        Order: alerts_alerts.""timestamp"" DESC"
"        ->  Index Scan using _hyper_1_234_chunk_alerts_alerts_timestamp_idx_1 on _hyper_1_234_chunk  (cost=0.29..2262.68 rows=1 width=89) (actual time=5.795..5.796 rows=0 loops=1)"
"              Filter: ((username)::text = 'username_long_query'::text)"
"              Rows Removed by Filter: 30506"
"        ->  Index Scan using _hyper_1_233_chunk_alerts_alerts_timestamp_idx_1 on _hyper_1_233_chunk  (cost=0.29..4337.82 rows=1 width=91) (actual time=11.112..11.112 rows=0 loops=1)"
"              Filter: ((username)::text = 'username_long_query'::text)"
"              Rows Removed by Filter: 59534"
            [   ...     Cut redundant log lines here    ...    ]
"        ->  Index Scan using _hyper_1_156_chunk_alerts_alerts_timestamp_idx_1 on _hyper_1_156_chunk  (cost=0.42..11418.54 rows=2591 width=80) (never executed)"
"              Filter: ((username)::text = 'username_long_query'::text)"
"        ->  Index Scan using _hyper_1_155_chunk_alerts_alerts_timestamp_idx_1 on _hyper_1_155_chunk  (cost=0.29..7353.95 rows=749 width=84) (never executed)"
"              Filter: ((username)::text = 'username_long_query'::text)"
            [   ...     Cut redundant log lines here    ...    ]
"Planning Time: 13.154 ms"
"Execution Time: 36129.923 ms"

Agora, isto é Explain Analyzepara os nomes de usuário que são executados rapidamente:

"Limit  (cost=471.73..471.73 rows=1 width=458) (actual time=1.672..1.691 rows=1 loops=1)"
"  ->  Sort  (cost=471.73..472.76 rows=414 width=458) (actual time=1.671..1.689 rows=1 loops=1)"
"        Sort Key: _hyper_1_234_chunk.""timestamp"" DESC"
"        Sort Method: top-N heapsort  Memory: 27kB"
"        ->  Append  (cost=0.29..469.66 rows=414 width=457) (actual time=1.585..1.654 rows=210 loops=1)"
"              ->  Index Scan using _hyper_1_234_chunk_alerts_alerts_fleet_a3933a38_1 on _hyper_1_234_chunk  (cost=0.29..2.49 rows=1 width=372) (actual time=0.006..0.007 rows=0 loops=1)"
"                    Index Cond: ((username)::text = 'username_value'::text)"
"              ->  Index Scan using _hyper_1_233_chunk_alerts_alerts_fleet_a3933a38_1 on _hyper_1_233_chunk  (cost=0.29..2.37 rows=1 width=385) (actual time=0.006..0.006 rows=0 loops=1)"
"                    Index Cond: ((username)::text = 'username_value'::text)"
            [   ...     Cut redundant log lines here    ...    ]
"              ->  Seq Scan on _hyper_1_83_chunk  (cost=0.00..1.12 rows=1 width=504) (actual time=0.013..0.013 rows=0 loops=1)"
"                    Filter: ((username)::text = 'username_value'::text)"
"                    Rows Removed by Filter: 10"
"              ->  Seq Scan on _hyper_1_81_chunk  (cost=0.00..1.12 rows=1 width=504) (actual time=0.009..0.009 rows=0 loops=1)"
"                    Filter: ((username)::text = 'username_value'::text)"
"                    Rows Removed by Filter: 10"
"Planning Time: 899.811 ms"
"Execution Time: 2.613 ms"

Pesquisas preliminares sugerem fazer manutenção na tabela do banco de dados. Após executar o comando de vácuo, as consultas foram executadas novamente, mas os resultados não foram alterados.

Ressalta-se também que existem outros nomes de usuário que utilizam o planejamento “problemático”, mas o tempo de execução ainda é rápido.

Não sei como resolver essa discrepância no tempo de execução da consulta. Poderia ser útil adicionar outro índice, mas como sou novo no PostgreSQL, atualmente não tenho certeza sobre a melhor abordagem para isso.

postgresql

1 respostas

  1. Best Answer

    "Filtrar por uma coluna ( username), ordenar por outra ( timestamp), LIMIT 1!"

    É a antiga batalha entre duas abordagens possíveis:

    1. Percorra um índice em timestampe filtre os nomes de usuário corretos. O primeiro golpe completa a tarefa. Isso é o que acontece no seu primeiro plano:
    "        ->  Index Scan using _hyper_1_234_chunk_alerts_alerts_timestamp_idx_1 on _hyper_1_234_chunk  (cost=0.29..2262.68 rows=1 width=89) (actual time=5.795..5.796 rows=0 loops=1)"
"              Filter: ((username)::text = 'username_long_query'::text)"
"              Rows Removed by Filter: 30506"

    Apenas repetido repetidamente para todas as partições em sua hipertabela Timescale (eles chamam as partições de "pedaços").

    Funciona bem, a menos que o primeiro golpe esteja em um passado distante - como no caso em questão. Se o Postgres não tiver informações válidas para trabalhar (estatísticas de coluna: lista de valores mais comuns, n_distinctconfiguração), ele poderá cair nessa armadilha.

    1. Use um índice usernamepara recuperar todas as linhas do(s) nome(s) de usuário(s) fornecido(s), depois classifique e obtenha o mais recente. Isso é o que seu segundo plano mostra:
    "        ->  Append  (cost=0.29..469.66 rows=414 width=457) (actual time=1.585..1.654 rows=210 loops=1)"
"              ->  Index Scan using _hyper_1_234_chunk_alerts_alerts_fleet_a3933a38_1 on _hyper_1_234_chunk  (cost=0.29..2.49 rows=1 width=372) (actual time=0.006..0.007 rows=0 loops=1)"
"                    Index Cond: ((username)::text = 'username_value'::text)"

    Isso é (muito) mais eficiente, se houver poucas linhas qualificadas e até mesmo a mais recente estiver oculta em muitas linhas mais recentes e não qualificadas.

    Se suas consultas filtrarem apenas umausername de cada vez, um índice de várias colunas(username, timestamp DESC) seria perfeito.

    Mas a hipertabela Timescale que você tem (presumo) é particionada na timestampcoluna ("particionada no tempo em pedaços"). Isso é otimizado para consultas timestampiniciais, então a melhor estratégia se torna complicada. Normalmente, ainda é dito índice de múltiplas colunas. Então o Postgres/Timescale ainda precisa examinar cada pedaço (ou apenas o índice), começando pelo mais novo, até encontrar a primeira entrada para o arquivo username. Mas agora ele não precisa vasculhar todas as linhas apenas para não encontrar nada e declarar Rows Removed by Filter: 30506"- que são todas as linhas do pedaço do exemplo.

    Para apenas alguns nomes de usuário distintos, você pode subparticionar suas hipertabelas. No jargão florido da escala de tempo , "adicione uma dimensão de particionamento de espaço a uma hipertabela" . Mas isso é ineficiente para muitos nomes de usuário distintos.

    O melhor curso de ação depende do quadro completo: cardinalidades, frequência de gravação, distribuição de dados, recursos de hardware, configuração do servidor, Postgres e versão da escala de tempo, ... provavelmente além do escopo de uma simples pergunta aqui.

    Relacionado:

    • 2

relate perguntas