Como fazer a consulta `IN (...set...)` do Postgres de maneira otimizada com um grande conjunto de valores

O apêndice de limites informará que o número máximo de parâmetros de consulta é 65.535 e, se não houver memória, o limite para uma mensagem (consulta) é de meio GB.

Naturalmente, o desempenho irá piorar gradualmente à medida que a lista aumenta. Eu recomendaria enviar um único parâmetro de array em vez de milhares de valores individuais. Uma abordagem alternativa é colocar COPYos valores em uma tabela temporária e juntá-los a ela. Para tamanhos de lista normais, não vi nenhuma vantagem nisso, mas evita os limites e pode ser benéfico para listas enormes.

No final, você mesmo terá que avaliar isso. Se você precisar de listas enormes como essa, reavalie suas escolhas de design.

Fiz um pequeno benchmark.

Código fonte no pastebin

Tabela de teste: 10 milhões de linhas com (id INT PRIMARY KEY, s TEXT).

Resultados:

   0.055ms      1 rows Correlated SELECT * FROM test_array WHERE id =ANY(A
   0.042ms      1 rows Correlated SELECT * FROM test_array WHERE id IN (1)
   0.070ms      1 rows Correlated SELECT * FROM unnest(ARRAY[1]) id JOIN t
   0.045ms      1 rows     Random SELECT * FROM test_array WHERE id =ANY(A
   0.042ms      1 rows     Random SELECT * FROM test_array WHERE id IN (31
   0.070ms      1 rows     Random SELECT * FROM unnest(ARRAY[3146607]) id
   0.058ms     10 rows Correlated SELECT * FROM test_array WHERE id =ANY(A
   0.059ms     10 rows Correlated SELECT * FROM test_array WHERE id IN (1,
   0.085ms     10 rows Correlated SELECT * FROM unnest(ARRAY[1,2,3,4,5,6,7
   0.065ms     10 rows     Random SELECT * FROM test_array WHERE id =ANY(A
   0.062ms     10 rows     Random SELECT * FROM test_array WHERE id IN (66
   0.088ms     10 rows     Random SELECT * FROM unnest(ARRAY[6629054,48357
   0.184ms    100 rows Correlated SELECT * FROM test_array WHERE id =ANY(A
   0.183ms    100 rows Correlated SELECT * FROM test_array WHERE id IN (1,
   0.222ms    100 rows Correlated SELECT * FROM unnest(ARRAY[1,2,3,4,5,6,7
   0.247ms    100 rows     Random SELECT * FROM test_array WHERE id =ANY(A
   0.237ms    100 rows     Random SELECT * FROM test_array WHERE id IN (15
   0.258ms    100 rows     Random SELECT * FROM unnest(ARRAY[153046,957664
   1.442ms   1000 rows Correlated SELECT * FROM test_array WHERE id =ANY(A
   1.458ms   1000 rows Correlated SELECT * FROM test_array WHERE id IN (1,
   1.558ms   1000 rows Correlated SELECT * FROM unnest(ARRAY[1,2,3,4,5,6,7
   2.076ms   1000 rows     Random SELECT * FROM test_array WHERE id =ANY(A
   2.019ms   1000 rows     Random SELECT * FROM test_array WHERE id IN (90
   2.070ms   1000 rows     Random SELECT * FROM unnest(ARRAY[9047600,58146
  15.233ms  10000 rows Correlated SELECT * FROM test_array WHERE id =ANY(A
  14.536ms  10000 rows Correlated SELECT * FROM test_array WHERE id IN (1,
  15.389ms  10000 rows Correlated SELECT * FROM unnest(ARRAY[1,2,3,4,5,6,7
  62.936ms   9995 rows     Random SELECT * FROM test_array WHERE id =ANY(A
  47.661ms   9995 rows     Random SELECT * FROM test_array WHERE id IN (31
  36.861ms  10000 rows     Random SELECT * FROM unnest(ARRAY[3109119,87658
 421.528ms 100000 rows Correlated SELECT * FROM test_array WHERE id =ANY(A
 413.692ms 100000 rows Correlated SELECT * FROM test_array WHERE id IN (1,
  95.054ms 100000 rows Correlated SELECT * FROM unnest(ARRAY[1,2,3,4,5,6,7
 413.768ms  99482 rows     Random SELECT * FROM test_array WHERE id =ANY(A
 411.587ms  99482 rows     Random SELECT * FROM test_array WHERE id IN (33
 508.202ms 100000 rows     Random SELECT * FROM unnest(ARRAY[3364043,10450

Interpretação:

Não há diferença entre "WHERE id IN (...)" e "WHERE id =ANY(...)".

Como esse tipo de consulta é dimensionado? Presumo que a matriz não esteja indexada, então, presumivelmente, cada valor da matriz atinge o índice, fornecendo escala de O (nN), para n valores da matriz e N linhas da tabela.

Supondo que a coluna pesquisada esteja indexada, ele faz uma pesquisa de índice para cada valor do array, a um custo de O(log N). Com n valores de matriz, esse é um custo total de O (n log N). Como esperado, há um pequeno custo fixo para executar uma consulta e, em seguida, ela é dimensionada de forma praticamente linear com o número de linhas retornadas.

Incluí dois casos: "Correlacionado", onde os IDs das linhas recuperadas são consecutivos, e "Aleatório", onde são randomizados em toda a tabela. Como esperado, os vários caches (da CPU L1 ao cache de disco do sistema operacional) fazem seu trabalho, por isso é mais rápido recuperar dados com maior localidade de referência.

De qualquer forma, a 2 microssegundos por linha, a carga da CPU do banco de dados é bastante baixa.

No entanto, isso é executado em um SSD e a tabela é armazenada em cache na RAM. Em uma situação mais "real", onde partes da tabela não seriam armazenadas em cache, se você recuperar linhas aleatórias, poderá esperar um acesso aleatório por linha. Isso pode ser bem lento, dependendo do seu hardware, mas... isso não tem nada a ver com o próprio postgres. Isso depende inteiramente do seu sistema IO e de quão bem seus dados são armazenados em cache. Se você usa discos giratórios e os dados não estão armazenados em cache, e você não se importa particularmente com o fato de essa consulta ser o mais rápida possível, talvez dividi-la em listas menores de linhas reduza o lixo do disco.

Também incluí um terceiro caso de teste:

SELECT * FROM unnest(ARRAY[%s]) id JOIN test_array USING (id)

Quando o comprimento do array é muito grande, as outras consultas simplesmente fazem uma varredura sequencial paralela. Isso é muito rápido, porque o "Filtro onde id=ANY(...)" não é burro, ele usa algum tipo de pesquisa rápida como hashing ou bisect, não compara cada linha com cada valor do array.

Esta última consulta é interessante porque é uma junção, então o postgres a otimiza como uma junção, que pode ser mais rápida... ou mais lenta... em alguns casos.