我有一个将数据存储在JSONB列中的表。
现在,我想做的是查询该表并获取记录,这些记录具有键的特定值。
这很好用:
SELECT "documents".*
FROM "documents"
WHERE (data @> '{"type": "foo"}')
但是我想做的是,获取表中所有类型为foo
OR的行bar
。
我尝试了这个:
SELECT "documents".*
FROM "documents"
WHERE (data @> '{"type": ["foo", "bar"]}')
但这似乎不起作用。
我也尝试过这个:
SELECT "documents".*
FROM "documents"
WHERE (data->'type' ?| array['foo', 'bar'])
哪个可行,但是如果我指定类似的键,那么data->'type'
它将消除查询的动态性。
顺便说一句,我将Ruby on Rails与Postgres一起使用,因此所有查询都通过ActiveRecord
。这是这样的:
Document.where("data @> ?", query)
如果我指定了像data->'type'这样的键,它将失去查询的动态性。
我了解您在data
定义的列上有一个杜松子酒索引,如下所示:
CREATE INDEX ON documents USING GIN (data);
索引适用于此查询:
EXPLAIN ANALYSE
SELECT "documents".*
FROM "documents"
WHERE data @> '{"type": "foo"}';
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on documents (cost=30.32..857.00 rows=300 width=25) (actual time=0.639..0.640 rows=1 loops=1)
Recheck Cond: (data @> '{"type": "foo"}'::jsonb)
Heap Blocks: exact=1
-> Bitmap Index Scan on documents_data_idx (cost=0.00..30.25 rows=300 width=0) (actual time=0.581..0.581 rows=1 loops=1)
Index Cond: (data @> '{"type": "foo"}'::jsonb)
Planning time: 7.928 ms
Execution time: 0.841 ms
但不适用于此:
EXPLAIN ANALYSE
SELECT "documents".*
FROM "documents"
WHERE (data->'type' ?| array['foo', 'bar']);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------
Seq Scan on documents (cost=0.00..6702.98 rows=300 width=25) (actual time=31.895..92.813 rows=2 loops=1)
Filter: ((data -> 'type'::text) ?| '{foo,bar}'::text[])
Rows Removed by Filter: 299997
Planning time: 1.836 ms
Execution time: 92.839 ms
解决方案1.使用运算符@>
两次,索引将同时用于以下两种情况:
EXPLAIN ANALYSE
SELECT "documents".*
FROM "documents"
WHERE data @> '{"type": "foo"}'
OR data @> '{"type": "bar"}';
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on documents (cost=60.80..1408.13 rows=600 width=25) (actual time=0.222..0.233 rows=2 loops=1)
Recheck Cond: ((data @> '{"type": "foo"}'::jsonb) OR (data @> '{"type": "bar"}'::jsonb))
Heap Blocks: exact=2
-> BitmapOr (cost=60.80..60.80 rows=600 width=0) (actual time=0.204..0.204 rows=0 loops=1)
-> Bitmap Index Scan on documents_data_idx (cost=0.00..30.25 rows=300 width=0) (actual time=0.144..0.144 rows=1 loops=1)
Index Cond: (data @> '{"type": "foo"}'::jsonb)
-> Bitmap Index Scan on documents_data_idx (cost=0.00..30.25 rows=300 width=0) (actual time=0.059..0.059 rows=1 loops=1)
Index Cond: (data @> '{"type": "bar"}'::jsonb)
Planning time: 3.170 ms
Execution time: 0.289 ms
解决方案2.在上创建一个附加索引(data->'type')
:
CREATE INDEX ON documents USING GIN ((data->'type'));
EXPLAIN ANALYSE
SELECT "documents".*
FROM "documents"
WHERE (data->'type' ?| array['foo', 'bar']);
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on documents (cost=30.32..857.75 rows=300 width=25) (actual time=0.056..0.067 rows=2 loops=1)
Recheck Cond: ((data -> 'type'::text) ?| '{foo,bar}'::text[])
Heap Blocks: exact=2
-> Bitmap Index Scan on documents_expr_idx (cost=0.00..30.25 rows=300 width=0) (actual time=0.035..0.035 rows=2 loops=1)
Index Cond: ((data -> 'type'::text) ?| '{foo,bar}'::text[])
Planning time: 2.951 ms
Execution time: 0.108 ms
解决方案3。实际上,这是解决方案1的变体,其条件格式不同,可能更便于客户端程序使用:
EXPLAIN ANALYSE
SELECT "documents".*
FROM "documents"
WHERE data @> any(array['{"type": "foo"}', '{"type": "bar"}']::jsonb[]);
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on documents (cost=60.65..1544.20 rows=600 width=26) (actual time=0.803..0.819 rows=2 loops=1)
Recheck Cond: (data @> ANY ('{"{\"type\": \"foo\"}","{\"type\": \"bar\"}"}'::jsonb[]))
Heap Blocks: exact=2
-> Bitmap Index Scan on documents_data_idx (cost=0.00..60.50 rows=600 width=0) (actual time=0.778..0.778 rows=2 loops=1)
Index Cond: (data @> ANY ('{"{\"type\": \"foo\"}","{\"type\": \"bar\"}"}'::jsonb[]))
Planning time: 2.080 ms
Execution time: 0.304 ms
(7 rows)
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句