Início / dba / Perguntas / 249417
Accepted
Maxim Yefremov
Asked: 2019-09-24 05:22:47 +0800 CST

melhorar a velocidade da pesquisa difusa

  • 772

Configurei o índice de trigramas para pesquisa difusa no Postgres. Inseri 8500000 linhas nesta tabela com texto de 4 palavras.

CREATE DATABASE people;
\connect people;
CREATE TABLE person
(
    id   bigserial NOT NULL,
    name text      NULL
);

CREATE EXTENSION fuzzystrmatch;
CREATE EXTENSION pg_trgm;

CREATE INDEX name_trigram_idx ON person USING gin (name gin_trgm_ops);

INSERT INTO person (name)
SELECT CONCAT(g.name, ' ', g.name, ' ', g.name, ' ', (SELECT array_to_string(
                                                                     array(select substr(
                                                                                          'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789',
                                                                                          ((random() * (36 - 1) + 1)::integer),
                                                                                          1)
                                                                           from generate_series(1, 10)), '')))
FROM generate_series(1, 8500000) AS g (name);

Agora eu faço uma pesquisa difusa:

SELECT * FROM person WHERE name % '1200000'

E tenha resultados:

   id    |                name                
---------+------------------------------------
   12000 | 12000 12000 12000 CLXBHQVEKC
  120000 | 120000 120000 120000 CLXBHQVEKC
 1200000 | 1200000 1200000 1200000 CLXBHQVEKC
 1202000 | 1202000 1202000 1202000 CLXBHQVEKC
 1212000 | 1212000 1212000 1212000 CLXBHQVEKC
(5 rows)

Demora cerca de 1 segundo. Isso é bom, mas eu gostaria de tê-lo mais rápido (300ms).

Quais são as possíveis maneiras de otimizar a velocidade? Aqui está explain-analyzeo pedido:

EXPLAIN (ANALYZE) SELECT * FROM person WHERE name % '1200000'
                                                             QUERY PLAN                                                              
-------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on person  (cost=201.87..24804.13 rows=8499 width=42) (actual time=45.135..972.583 rows=5 loops=1)
   Recheck Cond: (name % '1200000'::text)
   Rows Removed by Index Recheck: 147980
   Heap Blocks: exact=25286
   ->  Bitmap Index Scan on name_trigram_idx  (cost=0.00..199.74 rows=8499 width=0) (actual time=40.833..40.840 rows=147985 loops=1)
         Index Cond: (name % '1200000'::text)
 Planning Time: 1.538 ms
 Execution Time: 973.046 ms
(8 rows)

E um link para o meu repositório de teste .

De um modo geral, eu gostaria de ter uma pesquisa à prova de erros de digitação para um texto com várias palavras que tenha cerca de 9 milhões de linhas e não deve responder mais do que 300ms.

PS para este experimento estou executando o Postgres no docker com 6 CPUs, 8GB de memória e 1GB de swap.

postgresql

1 respostas

  1. Best Answer

    Você cria a extensão fuzzystrmatch, mas nunca a usa. Está faltando um exemplo?

    Esse tipo de consulta é muito sensível ao contexto, portanto, você precisa testá-lo em dados de exemplo realistas com consultas realistas. Seu exemplo não parece muito realista. Por um lado, sua sequência aleatória é a mesma sequência aleatória em todas as linhas, não uma sequência aleatória diferente para cada uma. Além disso, ter pequenos inteiros embutidos em seus nomes parece bastante estranho.

    Sua consulta de '120000' também não parece muito realista, ou se for, é muito infeliz. Devido à repetição de zeros, é uma consulta de baixa complexidade e não oferece muito ao mecanismo de consulta. Se você está preocupado com o caso médio em vez do pior caso, é uma má escolha de exemplo.

    Usar o operador "%" em strings de comprimento bastante desigual raramente é a coisa certa a fazer. Talvez você queira "%>" ou "%>>" em vez disso. Foram adicionados ao pg_trgm em 9,6 e 11, respectivamente. Eles medem a semelhança da consulta com o bloco de substring mais semelhante da coluna, em vez de toda a coluna. Eles diferem porque %>> exige que o "pedaço" seja alinhado nos limites da palavra, enquanto %> não. Eles não apenas dão o que eu acho que são melhores resultados, mas também são muito mais rápidos (neste caso, não conheço em geral).

    Finalmente, sua consulta seria muito mais rápida se você aumentasse a configuração de pg_trgm.similarity_threshold. Claro, você também não obtém resultados, porque nenhum de seus dados é realmente semelhante a '120000'. Isso remonta à escolha do operador, pois a presença de 'CLXBHQVEKC' reduz o quão semelhante qualquer coisa pode ser ao usar "%".

    • 2

relate perguntas