我想允许在 ID 上的 JsonB 字段上建立索引，该 ID 深入到我们 Django 项目中的 json 数据的几个级别。 JSONB 数据如下所示：
"foreign_data":{
"some_key": val
"src_data": {
"VEHICLE": {
"title": "615",
"is_working": true,
"upc": "85121212121",
"dealer_name": "CryptoDealer",
"id": 1222551
}
}
}
我想在该字段上建立索引id使用 Django 视图但不知道如何实现这一点。如果有帮助的话，很高兴发布我的 Django ViewSet。
t=# create table d(i bigserial, j jsonb);
CREATE TABLE
t=# insert into d(j) select ('{"foreign_data":{
"some_key": '||g||',
"src_data": {
"VEHICLE": {
"title": "615",
"is_working": true,
"upc": "85121212121",
"dealer_name": "CryptoDealer",
"id": '||g||'
}
}
}}')::jsonb from generate_series(1,1222600) g;
INSERT 0 1222600
t=# create index ji on d (cast (j->'foreign_data'->'src_data'->'VEHICLE'->>'id' as int));
CREATE INDEX
为了使用这种基于 fn() 的索引，您必须在查询中“重复”函数：
t=# explain analyze select * from d
where cast (j->'foreign_data'->'src_data'->'VEHICLE'->>'id' as int) = 1222551;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------
Index Scan using ji on d (cost=0.43..8.45 rows=1 width=215) (actual time=0.021..0.021 rows=1 loops=1)
Index Cond: ((((((j -> 'foreign_data'::text) -> 'src_data'::text) -> 'VEHICLE'::text) ->> 'id'::text))::integer = 1222551)
Planning time: 1.585 ms
Execution time: 0.045 ms
(4 rows)
正如你所看到的，成本很小，而且执行比索引便宜。但如果你“跳过”手续并运行：
t=# explain analyze select * from d
where j->'foreign_data'->'src_data'->'VEHICLE'->>'id' = '1222551';
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
Gather (cost=1000.00..50122.31 rows=6113 width=215) (actual time=335.996..336.000 rows=1 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Parallel Seq Scan on d (cost=0.00..48511.01 rows=2547 width=215) (actual time=223.548..332.213 rows=0 loops=3)
Filter: (((((j -> 'foreign_data'::text) -> 'src_data'::text) -> 'VEHICLE'::text) ->> 'id'::text) = '1222551'::text)
Rows Removed by Filter: 407533
Planning time: 0.096 ms
Execution time: 343.090 ms
(8 rows)
索引将不会被使用
本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系:hwhale#tublm.com(使用前将#替换为@)
