Django-postgres。如何在一个JsonB字段上创建一个索引[英] Django - postgres: How to create an index on a JsonB field

本文是小编为大家收集整理的关于Django-postgres。如何在一个JsonB字段上创建一个索引的处理方法,想解了Django-postgres。如何在一个JsonB字段上创建一个索引的问题怎么解决?Django-postgres。如何在一个JsonB字段上创建一个索引问题的解决办法?那么可以参考本文帮助大家快速定位并解决问题。

问题描述

我想允许在JSONB字段上进行ID索引,该ID是我们Django项目中JSON数据深处的几个层次.这是JSONB数据的样子:

  "foreign_data":{
      "some_key": val
      "src_data": {
              "VEHICLE": {
                  "title": "615",
                  "is_working": true,
                  "upc": "85121212121",
                  "dealer_name": "CryptoDealer",
                  "id": 1222551
              }
        }
 }

我想使用django视图在字段上索引id,但不确定如何实现.如果有帮助,很高兴发布我的Django视图集.

推荐答案

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)

索引将不使用

本文地址:https://www.itbaoku.cn/post/1763979.html