jgm's questions

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.

Eu tenho uma tabela JSON muito simples que preencho com alguns dados de amostra:

CREATE TABLE jsonthings(d JSONB NOT NULL);

INSERT INTO jsonthings VALUES ('{"name":"First","tags":["foo"]}');
INSERT INTO jsonthings VALUES ('{"name":"Second","tags":["foo","bar"]}');
INSERT INTO jsonthings VALUES ('{"name":"Third","tags":["bar","baz"]}');
INSERT INTO jsonthings VALUES ('{"name":"Fourth","tags":["baz"]}');

CREATE INDEX ON jsonthings USING GIN(d);

E estou tentando usar o índice ao executar um arquivo SELECT. Um simples SELECTpara obter as linhas onde o valor é um único item funciona muito bem:

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

Mas ao tentar executar uma consulta que corresponda a mais de um valor name, não consigo descobrir como usar o índice. Eu tentei:

SELECT d FROM jsonthings WHERE d->>'name' = ANY(ARRAY['First', 'Second']);
SELECT d FROM jsonthings WHERE d->'name' ?| ARRAY['First', 'Second'];
SELECT d FROM jsonthings WHERE d#>'{name}' ?| ARRAY['First','Second'];

e todos eles mostram uma varredura sequencial da tabela (estou usando enable_seqscan=falsepara forçar o uso do índice, se possível). Existe alguma maneira de reescrever a consulta para que ela use um índice? Estou ciente de que poderia fazer:

SELECT * FROM jsonthings WHERE d @> '{"name":"First"}' OR d @> '{"name":"Second"}';

mas então eu tenho uma consulta de comprimento variável e estou passando por JDBC, então perderia os benefícios da consulta sendo um PreparedStatement.

Também estou interessado em ver uma consulta semelhante em qualquer um dos vários itens da tagschave, por exemplo:

SELECT d FROM jsonthings WHERE d @> '{"tags":["foo"]}' OR d @> '{"tags":["bar"]}';

mas usando um ARRAYem vez de várias condições e usando um índice.

Isso está no PostgreSql 9.4.