current community

your communities

more stack exchange communities

Stack Exchange
tour help
Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 3 down vote favorite

I have a very simple JSON table which I populate with some sample data:

CREATE TABLE jsonthings(d JSONB NOT NULL);

INSERT INTO jsonthings VALUES ('{"name":"First","tags":["foo"]}');
INSERT INTO jsonthings VALUES ('{"name":"Second","tags":["foo","bar"]}');
INSERT INTO jsonthings VALUES ('{"name":"Third","tags":["bar","baz"]}');
INSERT INTO jsonthings VALUES ('{"name":"Fourth","tags":["baz"]}');

CREATE INDEX ON jsonthings USING GIN(d);

And am attempting to use the index when running a SELECT. A simple SELECT to obtain the rows where the value is a single item works just fine:

SELECT d FROM jsonthings WHERE d @> '{"name":"First"}';

But when attempting to run a query which matches more than one value of name I can't find out how to use the index. I've tried:

SELECT d FROM jsonthings WHERE d->>'name' = ANY(ARRAY['First', 'Second']);
SELECT d FROM jsonthings WHERE d->'name' ?| ARRAY['First', 'Second'];
SELECT d FROM jsonthings WHERE d#>'{name}' ?| ARRAY['First','Second'];

and all of them show a sequential scan of the table (I'm using enable_seqscan=false to force index use if possible). Is there some way I can rewrite the query so that it uses an index? I'm aware that I could do:

SELECT * FROM jsonthings WHERE d @> '{"name":"First"}' OR d @> '{"name":"Second"}';

but then I have a variable-length query and I'm going through JDBC so would then lose the benefits of the query being a PreparedStatement.

I'm also interested in seeing a similar query against any of a number of items in the tags key, e.g.:

SELECT d FROM jsonthings WHERE d @> '{"tags":["foo"]}' OR d @> '{"tags":["bar"]}';

but using an ARRAY rather than multiple conditions and using an index.

This is on PostgreSql 9.4.

share|improve this question
    
You don't have high selectivity. You need around 2-5% data of recordset to enable indexes. Put more records and then maybe your query analyizer will choose index over sequential scan. –  Mladen Uzelac Jan 22 at 22:25
    
Thanks for the comment. I set enable_seqscan to false to force index use so the lack of data isn't the issue. Although I did add another ten million rows during testing to make sure... –  jgm Jan 22 at 22:37
    
Please post your explain plan on explain.depesz.com –  Mladen Uzelac Jan 22 at 23:06

1 Answer 1

up vote 1 down vote accepted

From docs (http://www.postgresql.org/docs/9.4/static/datatype-json.html) try to use expression index:

CREATE INDEX idx_jsonthings_names ON jsonthings USING gin ((d -> 'name'));
SELECT d FROM jsonthings WHERE d @> '{"name": ["First", "Second"]}';
share|improve this answer
1  
Yeah looks like I do have to use a separate index, which seems odd given that the index obviously exists already and is used in the single-item query. And the query I need to use is SELECT d FROM jsonthings WHERE d->'name' ?| ARRAY['First', 'Second']; otherwise the index is not used. Thanks. –  jgm Jan 23 at 12:03

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.