jsonb数据的排序依据json元素未使用索引(Btree/GIN)- postgresql

p4tfgftt  于 2022-12-12  发布在  PostgreSQL
关注(0)|答案(1)|浏览(143)

I have below table in postgresql which stored JSON data in jsonb type of column

CREATE TABLE "Trial" (
  id SERIAL PRIMARY KEY,
  data jsonb
);

Below is the sample json structure

{
"id": "000000007001593061",
"core": {
    "groupCode": "DVL",
    "productType": "ZDPS",
    "productGroup": "005001000"
},
"plants": [
    {
        "core": {
            "mrpGroup": "ZMTS",
            "mrpTypeDesc": "MRP",
            "supLeadTime": 777
        },
        "storageLocation": [
            {
                "core": {
                    "storageLocation": "H050"
                }
            },
            {
                "core": {
                    "storageLocation": "H990"
                }
            },
            {
                "core": {
                    "storageLocation": "HM35"
                }
            }
        ]
    }
],
"discriminator": "Material"
}

There are around 8 million records with similar kind of json data.
I have created GIN Index as well as also tried json element specific BTree index

CREATE INDEX idx_trial_data_jsonpath ON "Trial" USING GIN (data jsonb_path_ops);

Also tried B-Tree index for specific json element that I wanted to use in order by

CREATE INDEX idx_trial_data_discriminator ON "Trial" USING btree ((data ->> 'discriminator'));

But seems order by is ignoring indexes for jsonb column, below is the query and it's execution plan where I can clearly see that sequential process behind the query execution instead of any index even through created it. Need assistance if anybody knows why order by is not using GIN or B-Tree index created for JSOB column

explain analyze 
Select id,data 
from "Trial" 
order by data->'discriminator' desc 
limit 100

Execution Plan of order by query
Need assistance on order by query to use index for jsonb column

owfi6suc

owfi6suc1#

您的索引与查询不匹配。->>->是不同的运算符。请使索引与查询匹配(反之亦然),然后即可使用该索引。

相关问题