I have a simple table
records that contains a primary key (
id) and a jsonb field (
data). The structure of the
jsonb is consistent across all rows. I have created a view (
metadata) for this table which extracts a particular value
category, which I can then run queries against.
CREATE VIEW metadata AS SELECT id as id, data -> 'some_key' -> 'some_array' ->> 0 as category FROM records;
(the data structure is not controlled by me)
I can use this VIEW to perform queries like:
SELECT category FROM metadata WHERE category = 'category_0';
and it behaves as expected.
I was experiencing slow performance out of the above query (and similar equality queries), so I added an index as follows:
CREATE INDEX metadata_category_idx ON records ((data -> 'some_key' -> 'some_array ->> 0));
This has not improved my performance as I would have expected when I query for a particular category using string equality. It should be noted that for "categories" with a small number of rows,
EXPLAIN ANALYZE tells me its using the index, however for "categories" with a large number of rows, it falls back to sequential searching. There are approximately 350k rows in my table. I've tried each type of index (i.e. GIN, GIST) with no measured benefits.
I intend to strictly use string equality when querying this view, and I have little control over the structure of the json object in
What would be the best way to construct my index such that the above query can run more efficiently?