Eu sou um novato para SQL e PostgreSQL. Estou tentando descobrir como obter esse tipo de consulta para utilizar um índice para a operação do trigrama. UsandoPostgreSQL 12.7 on x86_64-pc-linux-gnu
A ideia básica é pegar uma frase de pesquisa, dividi-la em palavras distintas e então ver quantas correspondências de 'semelhança' encontramos na coluna do banco de dados de nomes prontos para pesquisa. Quanto mais palavras tiverem semelhança com palavras encontradas no nome pronto para pesquisa, maior será a pontuação. Também consideramos a frase de pesquisa geral em relação ao nome original, como um multiplicador de um 'aumento' ao peso.
A tabela dpl_base tem 71.000 linhas e se parece com isso:
A tabela dpl_codes tem 100 linhas e se parece com isso:
Até agora tentei os dois:
create index trgm_idx_gist_dpl_base on dpl_base using gist (denied_name_searchable, denied_name_original gist_trgm_ops);
create index trgm_idx_gin_dpl_base on dpl_base using gin (denied_name_searchable, denied_name_original gin_trgm_ops);
Junto com vários outros índices 'padrão'. Com ou sem os índices, a consulta EXPLAIN ANALYZE fornece o mesmo plano exato. Assim, os índices parecem não fazer diferença. As consultas são executadas rapidamente, geralmente em menos de 3 segundos. Talvez eu esteja perseguindo algo que não preciso... estou apenas tentando aprender como indexar corretamente para uma consulta deste design:
SET pg_trgm.similarity_threshold = 0.35;
SELECT
/* create weighting value for the distinct-word hits within the SEARCHABLE column */
/* multiply by the similarity value for the original search phrase, against the ORIGINAL column */
(
('BAD' % ANY(STRING_TO_ARRAY(UPPER(DPLB.DENIED_NAME_SEARCHABLE),' ')))::int +
('ACTOR' % ANY(STRING_TO_ARRAY(UPPER(DPLB.DENIED_NAME_SEARCHABLE),' ')))::int
)
* (-(DPLB.DENIED_NAME_ORIGINAL <-> 'Bad Actor') + 1) AS WEIGHT,
/* add in the remaining columns from our two tables */
DPLB.DENIED_NAME_ORIGINAL, DPLC.DENIAL_REASON
FROM DPL_BASE DPLB
INNER JOIN DPL_CODES DPLC ON DPLB.DENIAL_CODE = DPLC.DENIAL_CODE
WHERE
/* must have at least one hit from our distinct words, in the SEARCHABLE column */
(
('Bad' % ANY(STRING_TO_ARRAY(UPPER(DPLB.DENIED_NAME_SEARCHABLE),' ')))::int +
('Actor' % ANY(STRING_TO_ARRAY(UPPER(DPLB.DENIED_NAME_SEARCHABLE),' ')))::int
) > 0
ORDER BY WEIGHT DESC, DPLB.DENIED_NAME_ORIGINAL ASC;
Aqui está um exemplo de um plano de consulta. Quaisquer dicas ou sugestões sobre (a) maneira correta de indexar e/ou (b) melhor design ou otimização de consulta - seriam muito apreciadas.
|QUERY PLAN |
|-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
|Gather Merge (cost=9448.31..11733.27 rows=19584 width=62) (actual time=525.228..529.633 rows=204 loops=1) |
| Workers Planned: 2 |
| Workers Launched: 2 |
| -> Sort (cost=8448.29..8472.77 rows=9792 width=62) (actual time=519.954..520.104 rows=68 loops=3) |
| Sort Key: (((((('YOUTH'::text % ANY (string_to_array(upper((dplb.denied_name_searchable)::text), ' '::text))))::integer + (('SOCIETY'::text % ANY (string_to_array(upper((dplb.denied_name_searchable)::text), ' '::text))))::integer))::double precision * ((- ((dplb.denied_name_original)::text <-> 'Youth Society'::text)) + '1'::double precision))) DESC, dplb.denied_name_original|
| Sort Method: quicksort Memory: 34kB |
| Worker 0: Sort Method: quicksort Memory: 34kB |
| Worker 1: Sort Method: quicksort Memory: 34kB |
| -> Hash Join (cost=4.25..7799.21 rows=9792 width=62) (actual time=23.524..519.630 rows=68 loops=3) |
| Hash Cond: (dplb.denial_code = dplc.denial_code) |
| -> Parallel Seq Scan on dpl_base dplb (cost=0.00..7229.60 rows=9792 width=70) (actual time=22.937..516.516 rows=68 loops=3) |
| Filter: (((('YOUTH'::text % ANY (string_to_array(upper((denied_name_searchable)::text), ' '::text))))::integer + (('SOCIETY'::text % ANY (string_to_array(upper((denied_name_searchable)::text), ' '::text))))::integer) > 0) |
| Rows Removed by Filter: 23432 |
| -> Hash (cost=3.00..3.00 rows=100 width=38) (actual time=0.401..0.407 rows=100 loops=3) |
| Buckets: 1024 Batches: 1 Memory Usage: 15kB |
| -> Seq Scan on dpl_codes dplc (cost=0.00..3.00 rows=100 width=38) (actual time=0.044..0.216 rows=100 loops=3) |
|Planning Time: 0.399 ms |
|Execution Time: 530.078 ms
Transformar booleanos em inteiros e depois fazer aritmética neles certamente estragará a indexação.
Deve ser a mesma coisa que:
apenas o último tem uma chance muito maior de ser indexado. Também,
Deve ser semelhante, mas não exatamente igual a
Mas novamente tem pelo menos alguma chance de usar um índice. Alternativamente, decomponha sua tabela em uma tabela diferente que tenha uma linha para cada elemento de
string_to_array(upper((denied_name_searchable)::text), ' '::text)para que você não precise decompô-la rapidamente.Finalmente,
o operador de índice não distribui sobre
,. Você precisa especificá-lo para cada coluna. Portanto, esse índice não pode ser usado para pesquisar trigramas em "denied_name_searchable". Além disso, não parece haver nenhum ponto em incluir "denied_name_original" no índice em primeiro lugar.