How to Optimize Postgres JSONB Array Queries with GIN Indexes?

How to Use GIN Indexes with JSONB Arrays in Postgres

Problem:
When querying JSONB arrays in Postgres, a GIN index using events_gin_idx ON some_table USING GIN (events); may not optimize queries that search for specific array elements.

Cause:
The query is converting the array elements to JSON array elements using jsonb_array_elements(events) AS e, which disrupts the indexing.

Proper Indexing for Postgres 12 or Later:
To create a GIN index that supports queries with path expressions, use the jsonb_path_ops operator class:

CREATE INDEX locations_events_gin_idx ON locations USING gin (events jsonb_path_ops);

Query Syntax for Postgres 12 or Later:
Use the @? operator to test for array elements that match a condition:

SELECT *
FROM locations
WHERE events @? '$[*] ? (@.event_slug == "test_1")
                      ? (@.end_time.datetime() < "2014-10-13".datetime()'

Query Syntax for Older Versions of Postgres:
For versions prior to Postgres 12, you can use the @> operator with a partial JSON object:

SELECT *
FROM locations
WHERE events @> '[{ "event_slug": "test_1"}]';

Advanced Solution Using Materialized View:
If the basic indexing solutions are not performant enough, consider creating a materialized view:

CREATE MATERIALIZED VIEW loc_event AS
SELECT l.location_id, e.event_slug, e.end_time  -- start_time not needed
FROM locations l, jsonb_populate_recordset(null::event_type, l.events) e;

CREATE INDEX loc_event_idx ON loc_event (event_slug, end_time, location_id);

Materialized View Query:

SELECT *
FROM loc_event
WHERE event_slug = 'test_1'
AND end_time >= '2014-10-30 14:04:06 -0400'::timestamptz;

The above is the detailed content of How to Optimize Postgres JSONB Array Queries with GIN Indexes?. For more information, please follow other related articles on the PHP Chinese website!