How to Index/Query JSONB Columns with Arbitrary Keys for Efficient Membership Queries in PostgreSQL?
0
votes
1
answer
93
views
In Postgres, how do I properly index/query
jsonb
columns that only contain key-value pairs where the keys are arbitrary, to speed up membership operations?
I have a table structured like this:
CREATE TABLE "assets" (
"asset_id" text NOT NULL,
"customer_id" text NOT NULL,
"customer_asset_id" test NOT NULL,
"attributes" jsonb NOT NULL,
CONSTRAINT "asset_pk" PRIMARY KEY ("asset_id"),
CONSTRAINT "asset_uc" UNIQUE ("tenant_id", "customer_asset_id")
);
This table is the driving table for all queries. A typical query (with query parameters resolved) looks like this:
SELECT
"asset_id",
"customer_id",
"attributes",
"issues"."opened_ts",
"issues"."issue_title"
FROM
"assets"
JOIN
/*
Multiple assets can be assigned to the same issue.
The insight_ids are Postgres UUIDs.
*/
"asset_issue" USING ("asset_id")
JOIN
"issues" USING ("issue_id", "customer_id")
WHERE
"customer_id" = 'someCustomerId'
/*
This block is generated dynamically based on what asset attributes the customer wishes to filter on.
The key names and the values are supplied via query parameters.
Keys and values are properly escaped JSON strings.
*/
AND
(
"assets"."attributes"->'someCustomerKey1' IN ('"someCustomerKey1Value1"', '"someCustomerKey1Value2"')
AND
"assets"."attributes"->'someCustomerKey2' IN ('"someCustomerKey2Value1"', '"someCustomerKey2Value2"')
)
/*
Additional predicates have been elided.
Note that they are mostly temporal range predicates:
AND
(COALESCE(:openedTsLowerBound, null) IS NULL OR "insight"."opened_ts" >= :openedTsLowerBound)
AND
(COALESCE(:openedTsUpperBound, null) IS NULL OR "insight"."opened_ts" < :openedTsUpperBound)
)
*/
ORDER BY
"issues"."opened_ts" DESC;
How can I index the attributes
column (or additional columns) effectively to improve the performance of such queries, considering that the keys within the jsonb
data are arbitrary?
Is IN
even the best way to go about this?
Would adding extended statistics help?
Note that customer asset counts range greatly from a handful to tens of thousands.
So the number of issues per customer ranges greatly into hundreds of thousands per customer.
Asked by Jeff
(111 rep)
Jul 12, 2024, 03:23 PM
Last activity: Jul 13, 2024, 12:06 AM
Last activity: Jul 13, 2024, 12:06 AM