current community

your communities

more stack exchange communities

company blog
Stack Exchange Inbox Reputation and Badges
tour help
Sign up ×
Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. Join them; it only takes a minute:
up vote 0 down vote favorite

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]';
share|improve this question
    
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 Dec 18 '15 at 18:09
    
Can you share the query plan generated (EXPLAIN ...)? – user3137702 Dec 18 '15 at 18:23
    
Sure thing: " Filter: (((data -> 'email'::text) ->> 'main'::text) = '[email protected]'::text)" – Wes Tyler Dec 18 '15 at 19:31
1  
wiki.postgresql.org/wiki/Slow_Query_Questions – a_horse_with_no_name Dec 18 '15 at 23:02
up vote 1 down vote

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"]}'
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.