Sort by json element for jsonb data is not using index (Btree/GIN) - postgresql

Question

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

The execution plan is better shared as formatted text. Paste the plan (as text) into your question and to make sure you preserve the indention of the plan. put ``` on the line before the plan and on a line after the plan. — user330315
– user330315, Commented Dec 8, 2022 at 10:28

jjanes · Accepted Answer · 2022-12-08 14:45:49Z

0

Your index does not match your query. ->> and -> are different operators. Make the index match the query or vice versa and it can be used.

answered Dec 8, 2022 at 14:45

jjanes

44.9k5 gold badges39 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Sort by json element for jsonb data is not using index (Btree/GIN) - postgresql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related