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
1条答案
按热度按时间owfi6suc1#
您的索引与查询不匹配。
->>
和->
是不同的运算符。请使索引与查询匹配(反之亦然),然后即可使用该索引。