jgm's questions

我正在尝试JSONB与 JDBC 一起使用，这意味着我必须避免使用任何使用“？”的运算符。字符（因为 PostgreSQL JDBC 驱动程序对此字符没有转义）。取一张简单的表格：

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'));

使用命令行，我可以运行一个简单的选择，并按预期使用索引：

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)

由于我无法使用该?字符，因此我使用了支持运算符的功能?。但是，它没有使用索引：

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)

为什么会发生这种情况，我该怎么做才能使函数使用索引？请注意，实际上该表中还有另外 10MM 行，我也已enable_seqscan关闭，所以这不是计划者决定不使用索引的情况。

为了回应评论，我尝试使用自定义运算符：

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

但这有同样的问题：

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)

更新

最新的 PostgreSql 驱动程序（截至 2015 年 3 月）具有转义?字符的能力，因此这种特定情况不再是问题。

我有一个非常简单的 JSON 表，其中填充了一些示例数据：

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);

并且在运行SELECT. 一个简单SELECT的获取值为单个项目的行就可以了：

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

但是，当尝试运行与多个值匹配的查询时，name我不知道如何使用索引。我试过了：

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'];

并且它们都显示了对表的顺序扫描（如果可能的话，我enable_seqscan=false用来强制使用索引）。有什么方法可以重写查询以使其使用索引？我知道我可以这样做：

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

但是后来我有一个可变长度的查询，并且我正在通过 JDBC，所以会失去查询作为 PreparedStatement 的好处。

我也有兴趣看到针对tags密钥中许多项目中的任何一个的类似查询，例如：

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

但是使用一个ARRAY而不是多个条件并使用一个索引。

这是在 PostgreSql 9.4 上。