我有下一个表结构:create table public.listings (id varchar(255) not null, data jsonb not null);
和下一个索引:create index listings_data_index on public.listings using gin(data jsonb_ops);
create unique index listings_id_index on public.listings(id);
alter table public.listings add constraint listings_id_pk primary key(id);
此行:
id | data
1 | {"attributes": {"ccid": "123", "listings": [{"vin": "1234","body": "Sleeper", "make": "International"}, { "vin": "5678", "body": "Sleeper", "make": "International" }]}}
用例需要检索listings
数组中与特定vin
匹配的特定项。
我将通过下一个查询来完成此操作:
SELECT elems
FROM public.listings, jsonb_array_elements(data->'attributes'->'listings') elems
WHERE id = '1' AND elems->'vin' ? '1234';
输出是我所需要的:{"vin": "1234","body": "Sleeper", "make": "International"}
现在我正处于优化这个查询的阶段,因为listings
数组中将有数百万行和多达10万个项。
当我对该查询运行解释时,显示如下:
Nested Loop (cost=0.01..2.53 rows=1 width=32)
-> Seq Scan on listings (cost=0.00..1.01 rows=1 width=32)
Filter: ((id)::text = '1'::text)
-> Function Scan on jsonb_array_elements elems (cost=0.01..1.51 rows=1 width=32)
Filter: ((value -> 'vin'::text) ? '1234'::text)
我想知道为它构造索引的正确方法是什么,或者我是否需要将查询修改为另一个更有效的查询。
谢谢大家!
1条答案
按热度按时间mspsb9vt1#
第一:对于这么小的表,您永远不会看到PostgreSQL使用索引。您需要尝试使用实际的数量。第二:尽管PostgreSQL很乐意为
id
上的条件使用索引,但无论如何编写,它都不能为这样的JSON搜索使用索引。