Postgres json index has no effect on query speed

Question

I have been trying to create an index on a jsonb document all morning but I can't see any benefit from the index.

My table:

CREATE TABLE modelling.triangle(id serial, data JSONB)

Here is my insert:

    INSERT INTO modelling.triangle(data)
    SELECT
    json_build_object(
              'x', generator.x,
              'y', generator.y,
              'array', generator.array_data)::jsonb
    FROM (
        SELECT 
        generate_series(1,10000) x, 
        generate_series(1,10000) y, 
        array_to_json(array_agg(array_elements)) array_data
        FROM (
            SELECT 
            generate_series(1,99) as key, 
            generate_series(1,99) as value
        ) array_elements
    ) as generator

And the index:

CREATE INDEX idxgintags ON modelling.triangle USING gin ((data -> 'x') jsonb_path_ops);

And the select:

EXPLAIN ANALYZE 
SELECT data->'array'
FROM modelling.triangle
WHERE data@>'{"x":10}'

Thank you.

NuLo · Accepted Answer · 2015-09-03 19:41:52Z

1

Your query isn't using the created index because that index was created based on an expression and you do not use the expression. The right query would be:

EXPLAIN ANALYZE 
SELECT data->'array'
FROM modelling.triangle
WHERE data->'x' @> '10'

You can read more about this in JSON Types.

Another way would be to create an index on data field with jsonb_path_ops

CREATE INDEX idxgintags2 ON modelling.triangle USING gin (data  jsonb_path_ops);

This way you can use your query unmodified but the index will be much larger.

edited Sep 3, 2015 at 19:41

answered Sep 3, 2015 at 15:37

NuLo

1,5381 gold badge13 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Lepper Over a year ago

Ah thank you, I didn't know exactly how to use the indexes. My query has gone from 1000 ms down to .124. Cheers.

Collectives™ on Stack Overflow

Postgres json index has no effect on query speed

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related