Estou tentando otimizar a consulta com a cláusula:
WHERE LOWER(CAST(\"my_table\".\"id\" as TEXT) LIKE '%456%'
Resolvi criar um índice de trigramas para ele.
CREATE INDEX index_name ON my_table USING gin (id gin_trgm_ops);
Mas parece que não posso fazer isso para um tipo inteiro:
ERROR: operator class "gin_trgm_ops" does not accept data type bigint
É possível? Provavelmente algum tipo de conversão necessário durante a criação do índice ...
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.
SELECT json_array_elements('["one", "two"]'::json)
dá resultado
| json_array_elements | | :------------------ | | "um" | | "dois" |
Eu gostaria de ter o mesmo, mas sem as aspas:
one
two
Parece que não posso usar ->>aqui porque não tenho nomes de campos no JSON. É apenas um array de strings.
Versão do Postgres: PostgreSQL 10.0 em x86_64-apple-darwin, compilado por i686-apple-darwin11-llvm-gcc-4.2 (GCC) 4.2.1 (Baseado na Apple Inc. build 5658) (LLVM build 2336.11.00), 64- pedaço