Início / user-290665

Zak123's questions

Martin Hope
Zak123
Asked: 2024-05-23 05:54:43 +0800 CST
  • 8

Eu tenho uma tabela grande (cerca de 2,2 bilhões de linhas), a estrutura da tabela é básica:

    Column    |  Type   | Collation | Nullable |             Default              | Storage | Compression | Stats target | Description
--------------+---------+-----------+----------+----------------------------------+---------+-------------+--------------+-------------
 Id           | bigint  |           | not null | generated by default as identity | plain   |             |              |
 ItemId       | uuid    |           | not null |                                  | plain   |             |              |
 StartUtc     | integer |           | not null |                                  | plain   |             |              |
 EndUtc       | integer |           | not null |                                  | plain   |             |              |
 Price        | integer |           | not null |                                  | plain   |             |              |
Indexes:
    "PK_PriceHistoryEntry" PRIMARY KEY, btree ("Id")
    "IX_PriceHistoryEntry_ItemId" btree ("ItemId") CLUSTER

Todas as consultas são feitas na ItemIdcoluna, no formato:

SELECT "ItemId", "StartUtc", "EndUtc", "Price" FROM "PriceHistoryEntry" WHERE "ItemId" IN (...array of guids)

A tabela não é particionada porque cada consulta selecionada precisaria alcançar todas as partições, as consultas nunca são feitas apenas em determinados intervalos de datas, sempre no histórico completo.

O método de inserção geral é uma vez por semana, 10-20 milhões de entradas de histórico de preços são inseridas, a tabela é reagrupada no ItemIdíndice e, em seguida, as seleções têm alto desempenho para nossos propósitos (~ 100 ms para a pesquisa média que fazemos, que é de aproximadamente 250-400 GUIDs nos INcritérios que retornam aproximadamente 50 mil linhas).

No desenvolvimento testei isso com até 5 bilhões de linhas e até 1000 GUIDs nos INcritérios, sempre foi rápido. Ele estava funcionando perfeitamente há mais de um ano, mas hoje o PostgreSQL parou de usar o índice quando INcontém mais de 400 GUIDs e não consigo entender o porquê, plano de índice para 400 GUIDs:

Index Scan using "IX_PriceHistoryEntry_ItemId" on public."PriceHistoryEntry"  (cost=0.58..32570020.41 rows=23433784 width=28) (actual time=11.355..110.863 rows=160503 loops=1)
   Output: "ItemId", "StartUtc", "EndUtc", "Price"
   Index Cond: ("PriceHistoryEntry"."ItemId" = ANY ('{ac5aa227-8787-46fc-b34d-47017edc7d1f,*cut 398 guids*,16923b11-30b7-4311-bc54-3b2b1da314d0}'::uuid[]))
   Buffers: shared hit=828 read=2550
   I/O Timings: shared read=79.971
 Planning Time: 0.314 ms
 JIT:
   Functions: 4
   Options: Inlining true, Optimization true, Expressions true, Deforming true
   Timing: Generation 0.234 ms, Inlining 0.064 ms, Optimization 6.114 ms, Emission 4.823 ms, Total 11.235 ms
 Execution Time: 115.573 ms
(11 rows)

Time: 116.586 ms

mas se você usar GUIDs 401:

 Gather  (cost=1000.99..32292650.29 rows=23734217 width=28) (actual time=232.393..55599.947 rows=161872 loops=1)
   Output: "ItemId", "StartUtc", "EndUtc", "Price"
   Workers Planned: 2
   Workers Launched: 2
   Buffers: shared hit=7543 read=17088587
   I/O Timings: shared read=48381.022
   ->  Parallel Seq Scan on public."PriceHistoryEntry"  (cost=0.99..29918228.59 rows=9889257 width=28) (actual time=227.890..51672.274 rows=53957 loops=3)
         Output: "ItemId", "StartUtc", "EndUtc", "Price"
         Filter: ("PriceHistoryEntry"."ItemId" = ANY ('{bff9de7e-7f35-4f5d-88c5-2c342806d69b,*cut 399 guids*,618ce691-c8f0-46b2-8fd1-96a404bdda71}'::uuid[]))
         Rows Removed by Filter: 683791228
         Buffers: shared hit=7543 read=17088587
         I/O Timings: shared read=48381.022
         Worker 0:  actual time=329.642..50237.693 rows=54262 loops=1
           JIT:
             Functions: 4
             Options: Inlining true, Optimization true, Expressions true, Deforming true
             Timing: Generation 0.238 ms, Inlining 45.205 ms, Optimization 4.210 ms, Emission 3.867 ms, Total 53.520 ms
           Buffers: shared hit=2368 read=5516111
           I/O Timings: shared read=15615.749
         Worker 1:  actual time=122.393..49195.649 rows=52913 loops=1
           JIT:
             Functions: 4
             Options: Inlining true, Optimization true, Expressions true, Deforming true
             Timing: Generation 0.240 ms, Inlining 45.364 ms, Optimization 4.256 ms, Emission 3.936 ms, Total 53.796 ms
           Buffers: shared hit=2447 read=5377318
           I/O Timings: shared read=15380.362
 Planning Time: 0.396 ms
 JIT:
   Functions: 12
   Options: Inlining true, Optimization true, Expressions true, Deforming true
   Timing: Generation 0.767 ms, Inlining 90.645 ms, Optimization 16.254 ms, Emission 13.825 ms, Total 121.492 ms
 Execution Time: 55604.845 ms
(32 rows)

Time: 55606.114 ms (00:55.606)

Como eu disse, ao desenvolver isso, testei-o extensivamente com até 5 bilhões de linhas e mais de 1.000 GUIDs nos INcritérios, o que é significativamente mais do que o que está atualmente no banco de dados e nunca encontrei esse problema.

Eu tentei:

  • Reagrupando a mesa
  • ReindexaçãoIX_PriceHistoryEntry_ItemId
  • VACUUM ANALYZEna mesa
  • VACUUM FULLna mesa
  • Reconstruindo a tabela inteira compg_repack

Ainda se recusa a usar o índice agora. Alguma ideia do que aconteceu, como consertar e como garantir que isso nunca aconteça novamente?

Se eu desabilitar seq scan( set enable_seqscan = off;) ele usará o índice corretamente e retornará os resultados em 100ms... em vez dos 55 segundos que leva quando o PostgreSQL usa o plano que considera melhor. Desabilitar o seqscan parece não ser recomendado, se eu diminuí-lo, random_page_costele usará o índice para consultas um pouco maiores, mas para consultas particularmente grandes, INele ainda usará uma varredura de sequência. Preciso garantir que ele nunca use uma varredura sequencial, pois nunca será mais rápido para esta tabela.

postgresql