Início / dba / Perguntas / 316892
Accepted
Alessandro Jeanteur
Asked: 2022-09-15 08:26:31 +0800 CST

Como posso fazer consultas em conjuntos de dados distorcidos com mais desempenho no Postgres

  • 772

Eu tenho uma consulta aparentemente simples que infelizmente é extremamente lenta.

Acredito que sei por que é lento, mas não como torná-lo rápido, então gostaria de ver como isso poderia ser melhorado.

vamos chamar a tabela em questão de "PriceHistory", que rastreia o "preço" (número) para um "productId", tem milhões de linhas e milhares de entradas por productId.

Há um índice btree no productId e outro no preço.

Crucialmente (eu acho!) à medida que novos productIds são criados, seus dados de preço permanecem agrupados, então provavelmente há milhões de linhas relacionadas a diferentes productIds antes de chegar à primeira linha relacionada a um determinado productId.

Consulta super lenta:

EXPLAIN ANALYZE SELECT min(price) FROM PriceHistory WHERE productId = 'someId'

Result (cost=555.25..555.26 rows=1 width=32) (actual time=100084.212..100084.213 rows=1 loops=1)
 InitPlan 1 (returns $0)
 -> Limit (cost=0.43..555.25 rows=1 width=6) (actual time=100084.209..100084.209 rows=1 loops=1)
 -> Index Scan using "PriceHistory_btree_price" on "PriceHistory" (cost=0.43..2492270.00 rows=4492 width=6) (actual time=100084.207..100084.208 rows=1 loops=1)
 Index Cond: (price IS NOT NULL)
 Filter: ((productId)::text = 'someId'::text)
 Rows Removed by Filter: 1140612
Planning Time: 0.124 ms
Execution Time: 100084.230 ms

Consulta equivalente rápida que força o uso de um índice melhor

> EXPLAIN ANALYZE WITH x as (SELECT price_number FROM tradingcards_live."custom$0card_prices" where "card_custom_card" = '1348695171700984260__LOOKUP__1587446850514x224832321163624450') SELECT min(price_number) from x
Aggregate (cost=14964.82..14964.83 rows=1 width=32) (actual time=1584.004..1584.005 rows=1 loops=1)
 -> Index Scan using "PriceHistory_btree_productid" on "PriceHistory" (cost=0.56..14953.58 rows=4493 width=6) (actual time=0.909..1582.147 rows=4674 loops=1)
 Index Cond: ((productId)::text = 'someId'::text)
Planning Time: 0.149 ms
Execution Time: 1584.027 ms

Meu entendimento aqui é que as estatísticas básicas dizem ao postgres que o índice numérico do preço espera que ele atinja uma correspondência no productId "mais cedo", dado quantas linhas correspondem a algumId (neste caso, 4673 - então ~ dentro das primeiras 1000 linhas assumindo uma distribuição uniforme de 5 milhões de linhas), e talvez isso faça com que o postgres pense que é mais barato verificar os valores de preço até a primeira correspondência em vez de corresponder aos valores corretos do productId e fazer a agregação na memória.

Essa suposição está correta e como podemos fazer com que a consulta inicial escolha automaticamente o melhor índice, dada a distorção de dados - "valores que correspondem à coluna X são agrupados, não igualmente espalhados e altos o suficiente para que uma varredura percorrer muitas linhas primeiro" ?

postgresql postgresql-performance

2 respostas

  1. Sua avaliação do problema está correta: o PostgreSQL não sabe onde no disco estão armazenadas as muitas linhas que satisfazem a condição.

    A única maneira segura que consigo pensar é modificar a consulta para que o PostgreSQL não possa usar o índice:

    SELECT min(price + 0)
FROM PriceHistory
WHERE productId = 'someId';

    Mas é claro que isso também impedirá que o índice seja usado nos casos em que esse seria o plano mais rápido ...

    • 0
  2. Best Answer

    Você está correto, o planejador não sabe como as linhas estão agrupadas, então escolhe o índice errado.

    Você pode forçar o planejador a usar o melhor dos dois índices abaixo do ideal, como sugeriu Laurenz. Mas provavelmente seria melhor fornecer apenas o índice ideal, que estaria em(productid, price)

    • 0

relate perguntas