Início / dba / Perguntas / 334864
Accepted
Sebastian
Asked: 2024-01-15 17:24:52 +0800 CST

Contagem lenta do Postgresql com like

  • 772

Eu tenho uma mesa plana simples com aprox. 18 milhões de linhas. Agora preciso contar um campo com o operador ILIKE. Esta consulta retorna aproximadamente meio milhão de linhas. Quando o item de pesquisa é "mais estreito" (uma string de pesquisa mais longa), tudo é rápido.

SELECT count(*)::int
FROM "KbaData" AS k
WHERE k."KbaKey" ILIKE '1313%'

mas isso dura 30 anos, muito tempo. O índice neste campo é definido como:

CREATE INDEX IF NOT EXISTS "IX_KbaData_KbaKey"
    ON public."KbaData" USING gin
    ("KbaKey" COLLATE pg_catalog."default" gin_trgm_ops)
    TABLESPACE pg_default;

A análise retorna isto:

"Finalize Aggregate  (cost=3370561.68..3370561.69 rows=1 width=4) (actual time=35821.869..35832.270 rows=1 loops=1)"
"  ->  Gather  (cost=3370561.46..3370561.67 rows=2 width=8) (actual time=35821.690..35832.235 rows=3 loops=1)"
"        Workers Planned: 2"
"        Workers Launched: 2"
"        ->  Partial Aggregate  (cost=3369561.46..3369561.47 rows=1 width=8) (actual time=35787.156..35787.158 rows=1 loops=3)"
"              ->  Parallel Seq Scan on ""KbaData"" k  (cost=0.00..3364441.38 rows=2048033 width=0) (actual time=104.308..35644.469 rows=1498715 loops=3)"
"                    Filter: (""KbaKey"" ~~* '1313%'::text)"
"                    Rows Removed by Filter: 3271997"
"Planning Time: 0.231 ms"
"JIT:"
"  Functions: 14"
"  Options: Inlining true, Optimization true, Expressions true, Deforming true"
"  Timing: Generation 1.512 ms, Inlining 200.964 ms, Optimization 62.820 ms, Emission 48.232 ms, Total 313.528 ms"
"Execution Time: 35832.920 ms"

Como posso fazer isso melhor?

postgresql

1 respostas

  1. Best Answer

    Quando o item de pesquisa é "mais estreito" (uma string de pesquisa mais longa), tudo é rápido

    Contar 1/3 da tabela provavelmente será mais lento do que contar, digamos, 1/100.000 da tabela.

    Para este caso, com apenas um curinga a seguir no padrão, usar um índice btree com o operador text_pattern_ops provavelmente seria mais rápido, pois pode usar uma varredura somente de índice. (Mas ainda estará contando 1/3 da mesa, então não tenha muitas esperanças)

    As varreduras de bitmap podem usar varreduras somente de índice para consultas de contagem, exceto que o operador LIKE com trigramas sempre precisa ser verificado novamente, portanto isso não se aplicaria aqui.

    • 0

relate perguntas