Postgres jsonb query missing index?

Question

We have the following json documents stored in our PG table (identities) in a jsonb column 'data':

{
    "email": {
        "main": "[email protected]",
        "prefix": "aliasPrefix",
        "prettyEmails": ["stuff1", "stuff2"]
    },
    ...
}

I have the following index set up on the table:

CREATE INDEX ix_identities_email_main
  ON identities
  USING gin
  ((data -> 'email->main'::text) jsonb_path_ops);

What am I missing that is preventing the following query from hitting that index?? It does a full seq scan on the table... We have tens of millions of rows, so this query is hanging for 15+ minutes...

SELECT * FROM identities WHERE data->'email'->>'main'='[email protected]';

I've also tried data @> '{"email": {"main": "[email protected]"}}' But that not only misses the index as well, it isn't really the query I want anyways because of the other properties on the email object. — Wes Tyler
– Wes Tyler, Commented Dec 18, 2015 at 18:09
Sure thing: " Filter: (((data -> 'email'::text) ->> 'main'::text) = '[email protected]'::text)" — Wes Tyler
– Wes Tyler, Commented Dec 18, 2015 at 19:31

Dzmitry Savinkou · Accepted Answer · 2015-12-18 22:59:54Z

If you use JSONB data type for your data column, in order to index ALL "email" entry values you need to create following index:

CREATE INDEX ident_data_email_gin_idx ON identities USING gin ((data -> 'email'));

Also keep in mind that for JSONB you need to use appropriate list of operators;

The default GIN operator class for jsonb supports queries with the @>, ?, ?& and ?| operators

Following queries will hit this index:

SELECT * FROM identities
WHERE data->'email' @> '{"main": "[email protected]"}'
-- OR
SELECT * FROM identities
WHERE data->'email' @> '{"prefix": "aliasPrefix"}'

If you need to search against array elements "stuff1" or "stuff2", index above will not work , you need to explicitly add expression index on "prettyEmails" array element values in order to make query work faster.

CREATE INDEX ident_data_prettyemails_gin_idx ON identities USING gin ((data -> 'email' -> 'prettyEmails'));

This query will hit the index:

SELECT * FROM identities
WHERE data->'email' @> '{"prettyEmails":["stuff1"]}'

Collectives™ on Stack Overflow

Postgres jsonb query missing index?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related