How to use an index when using jsonb_array_elements in Postgres

Question

I have the next table structure:

create table public.listings (id varchar(255) not null, data jsonb not null);

And the next indexes:

create index listings_data_index on public.listings using gin(data jsonb_ops);

create unique index listings_id_index on public.listings(id);

alter table public.listings add constraint listings_id_pk primary key(id);

With this row:

id | data
1  | {"attributes": {"ccid": "123", "listings": [{"vin": "1234","body": "Sleeper", "make": "International"}, { "vin": "5678", "body": "Sleeper", "make": "International" }]}}

The use case needs to retrieve a specific item inside the listings array that matches a specific vin.

I am accomplishing that with the next query:

SELECT elems
FROM public.listings, jsonb_array_elements(data->'attributes'->'listings') elems
WHERE id = '1' AND elems->'vin' ? '1234';

The output is what I need:

{"vin": "1234","body": "Sleeper", "make": "International"}

Now I am in the phase of optimizing this query, since there will be millions of rows, and up to 100K items inside listings array.

When I run the explain over that query is shows this:

Nested Loop  (cost=0.01..2.53 rows=1 width=32)
  ->  Seq Scan on listings  (cost=0.00..1.01 rows=1 width=32)
        Filter: ((id)::text = '1'::text)
  ->  Function Scan on jsonb_array_elements elems  (cost=0.01..1.51 rows=1 width=32)
        Filter: ((value -> 'vin'::text) ? '1234'::text)

I wonder what would be the right way to construct an index for that, or if I need to modify the query to another that is more efficient.

Thank you!

Answer 1

First: with a table as small as that, you will never see PostgreSQL use an index. You need to try with realistic amounts. Second: while PostgreSQL will happily use an index for the condition on id, it can never use an index for such a JSON search, no matter how you write it.