Início / dba / Perguntas / 90002
Accepted
jgm
Asked: 2015-01-24 04:05:22 +0800 CST

O operador do PostgreSQL usa o índice, mas a função subjacente não

  • 772

Estou tentando usar JSONBcom JDBC, o que significa que tenho que evitar qualquer um dos operadores que usam o '?' (já que o driver PostgreSQL JDBC não tem escape para este caractere). Tomando uma tabela simples:

CREATE TABLE jsonthings(d JSONB NOT NULL);
INSERT INTO jsonthings VALUES
    ('{"name":"First","tags":["foo"]}')
  , ('{"name":"Second","tags":["foo","bar"]}')
  , ('{"name":"Third","tags":["bar","baz"]}')
  , ('{"name":"Fourth","tags":["baz"]}');
CREATE INDEX idx_jsonthings_name ON jsonthings USING GIN ((d->'name'));

Usando a linha de comando, posso executar uma seleção simples e usar o índice conforme o esperado:

EXPLAIN ANALYZE SELECT d FROM jsonthings WHERE d->'name' ? 'First';

                                                            QUERY PLAN                                                            
----------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on jsonthings  (cost=113.50..30236.13 rows=10000 width=61) (actual time=0.024..0.025 rows=1 loops=1)
   Recheck Cond: ((d -> 'name'::text) ? 'First'::text)
   Heap Blocks: exact=1
   ->  Bitmap Index Scan on idx_jsonthings_name  (cost=0.00..111.00 rows=10000 width=0) (actual time=0.015..0.015 rows=1 loops=1)
         Index Cond: ((d -> 'name'::text) ? 'First'::text)
 Planning time: 0.073 ms
 Execution time: 0.047 ms
(7 rows)

Como não posso usar o ?caractere, recorri a uma função que sustenta o ?operador. No entanto, não está usando o índice:

EXPLAIN ANALYZE SELECT d FROM jsonthings WHERE jsonb_exists(d->'name','First');
                                                            QUERY PLAN                                                            
----------------------------------------------------------------------------------------------------------------------------------
 Seq Scan on jsonthings  (cost=10000000000.00..10000263637.06 rows=3333334 width=61) (actual time=0.016..3135.119 rows=1 loops=1)
   Filter: jsonb_exists((d -> 'name'::text), 'First'::text)
   Rows Removed by Filter: 10000003
 Planning time: 0.051 ms
 Execution time: 3135.138 ms
(5 rows)

Por que isso está acontecendo e o que posso fazer para que a função use o índice? Observe que, na realidade, a tabela tem outras linhas de 10MM e também enable_seqscandesativei, portanto, este não é o caso do planejador decidir não usar o índice.

Em resposta a um comentário, tentei usar um operador personalizado :

CREATE OPERATOR ### (
  PROCEDURE = jsonb_exists,
  LEFTARG = jsonb,
  RIGHTARG = text,
  RESTRICT = contsel,
  JOIN = contjoinsel);

Mas isso tem o mesmo problema:

EXPLAIN ANALYZE SELECT d FROM jsonthings WHERE d->'name' ### 'First';
                                                           QUERY PLAN                                                           
--------------------------------------------------------------------------------------------------------------------------------
 Seq Scan on jsonthings  (cost=10000000000.00..10000263637.06 rows=10000 width=61) (actual time=0.012..3381.608 rows=1 loops=1)
   Filter: ((d -> 'name'::text) ### 'First'::text)
   Rows Removed by Filter: 10000003
 Planning time: 0.046 ms
 Execution time: 3381.623 ms
(5 rows)

Atualizar

O driver PostgreSql mais recente (em março de 2015) tem a capacidade de escapar do ?caractere, portanto, esse caso específico não é mais um problema.

postgresql index-tuning

1 respostas

  1. Best Answer

    Operadores com suporte e solução alternativa

    A classe de operador padrão para índices GIN em jsoncolunas jsonb_opssuporta apenas estes operadores (por documentação):

    Name        Indexed Data Type   Indexable Operators
...
jsonb_ops   jsonb               ? ?& ?| @>

    Você pode conseguir isso de outra maneira: crie uma função SQL simplesIMMUTABLE usando o ?operador, que pode ser embutido e usará o índice exatamente como o próprio operador:

    CREATE OR REPLACE FUNCTION jb_contains(jsonb, text)
  RETURNS bool AS
'SELECT $1 ? $2' LANGUAGE sql IMMUTABLE;

    Funciona, testei no Postgres 9.4...

    mal-entendidos

    No entanto , você está fazendo a pergunta errada . Existem dois equívocos básicos em sua pergunta.

    1. O jsonboperador ?não pode ser usado para pesquisar valores . Somente para chaves ou elementos de matriz . O manual:

      Descrição:
      a string existe como uma chave de nível superior dentro do valor JSON?

      Você pegou o operador errado , a WHEREcondição não pode funcionar:WHERE d->'name' ? 'First'

    2. O índice de expressão que você tem não faz sentido de qualquer maneira

      CREATE INDEX idx_jsonthings_name ON jsonthings USING GIN ((d->'name'));

      • A expressão d->'name'retorna um jsonbvalor. Você precisaria obter o valor como .d->>'name'text

      • Mas isso ainda seria inútil. Como o valor da namechave é uma string simples, um índice GIN (embora possível) não faz sentido para começar.

    Soluções

    Você não precisa do operador ?- portanto, também não há solução alternativa.
    Aqui estão duas maneiras que realmente funcionariam:

    1. Ative o índice GIN simples de use o operador "contém" @>:

      CREATE INDEX idx_jsonthings_d_gin ON jsonthings USING GIN (d);

SELECT d FROM jsonthings WHERE d @> '{"name":"First"}'

      Você pode até usar a classe de operador mais especializadajsonb_path_ops . Ver:

    2. Índice de expressão de árvore B d->>'email'e teste com o bom e velho =:

      CREATE INDEX idx_jsonthings_d_email ON jsonthings ((d->>'email'));

SELECT d FROM jsonthings WHERE d->>'email' = 'First';

    O segundo índice seria consideravelmente menor e a consulta mais rápida.

    • 3

relate perguntas