Início / dba / Perguntas / 247085
Accepted
maxTrialfire
Asked: 2019-09-06 09:10:17 +0800 CST

Classe de Operador PostgreSQL para suporte de índice de operador personalizado

  • 772

Estou tentando criar um índice que dará suporte a consultas que usam meu operador personalizado. Isso está no PostgreSQL 10.4.

O operador personalizado

Eu segui as dicas nesta resposta SO para criar um operador que executa a correspondência de estilo "LIKE" em elementos em um texto ARRAY.

CREATE FUNCTION reverse_like (text, text) returns boolean language sql 
as $$ select $2 like $1 $$;
    
CREATE OPERATOR <~~ ( function =reverse_like, leftarg = text, rightarg=text );

O operador acima me permite fazer coisas como

SELECT 'ab%' <~~ ANY('{"abc","def"}');

O esquema, índice e consulta

Eu tenho uma tabela com visitas de tráfego da web chamada sessionsque inclui uma coluna de matriz.

CREATE TABLE sessions
(
   session_id    varchar(24) NOT NULL,
   first_seen    timestamp,
   domains       varchar[]
);

Para consultar a coluna de domínios para ver se um determinado domínio (ou nome de domínio parcial/curinga) foi visitado, posso fazer o seguinte:

SELECT count(*)
FROM session_4070ba14_f081_41cb_9ef7_9dd385934da7
WHERE 'www.foo%' <~~ ANY(domains);

Eu quero acelerar as consultas acima com o índice GIN. Então eu criei o índice da seguinte forma:

CREATE INDEX idx_domains ON session USING GIN(domains);

A questão

Depois de executar analise na mesa e set enable_seqscan = false;não tenho sorte em fazer o Postgres empregar esse índice. Está sempre fazendo um seqscan. Ele usa o índice acima de operadores de matriz, @>mas não para o meu <~~operador personalizado.

Eu acho que é porque o índice GIN não sabe como lidar com meu operador personalizado - então eu preciso criar uma classe de operador e depois criar meu índice usando isso? Ou crio um índice funcional?

postgresql index

3 respostas

  1. Para suporte a trigramas, você pode tentar a extensão parray_gin

    WHERE domains @@> ARRAY['www.foo%'];

    Se você quer apenas fazer a correspondência de prefixo (mais eficiente do que a fornecida pelo trigrama), não acho que haja como fazer isso sem escrever algum código C para colar as peças. Acho que você trabalharia diretamente no tipo de matriz, portanto, não precisaria do ANY e, portanto, não se beneficiaria do operador reverse_like.

    • 1

  2. Você não poderá indexar uma expressão como esta:

    <constant> <operator> ANY(<array column>)

    Sua única chance seria definir um operador de tal forma que sua expressão se parecesse com:

    <array column> <operator> <constant>

    Mas escrever uma classe de operador GIN significa escrever uma extensão em C, e não acho que você queira ir tão longe.

    A solução fácil seria alterar seu modelo de dados para que você não use arrays para coisas assim.

    • 0
  3. Best Answer

    Acontece que eu compliquei isso pensando em um índice GIN. Um índice b-tree em todo o array funciona bem e suporta o operador personalizado <~~.

    
CREATE INDEX IF NOT EXISTS idx_domains2 ON session(domains );

select count(*)
from session
where 'www.foo%' <~~ ANY(domains);


    Finalize Aggregate  (cost=331523.11..331523.12 rows=1 width=8)
  ->  Gather  (cost=331522.90..331523.11 rows=2 width=8)
        Workers Planned: 2
        ->  Partial Aggregate  (cost=330522.90..330522.91 rows=1 width=8)
              ->  Parallel Index Only Scan using idx_domains2 on session  (cost=0.42..330200.52 rows=128952 width=0)
                    Filter: ('www.foo%'::text <~~ ANY ((domains)::text[]))
    • 0

relate perguntas