PostgreSQL jsonb indexing for optimized search performance

Question

I'm using PostgreSQL 10.1 jsonb data type and designing a JSON document of the following structure:

{
    "guid": "9c36adc1-7fb5-4d5b-83b4-90356a46061a",
    "name": "Angela Barton",
    "is_active": true,
    "company": "Magnafone",
    "address": "178 Howard Place, Gulf, Washington, 702",
    "registered": "2009-11-07T08:53:22 +08:00",
    "latitude": 19.793713,
    "longitude": 86.513373,
    "timestamp": 2001-09-28 01:00:00,
    "tags": [
        "enim",
        "aliquip",
        "qui"
    ]
}

I need to retrieve JSON documents by searching based on tags and sorted by timestamp.

I have read these documenations and it says jsonb_path_ops offers better performance:

To index tags, 1. gave an example:

CREATE INDEX idxgintags ON api USING GIN ((jdoc -> 'tags'));

However, the example uses jsonb_ops but I think it is better to use jsonb_path_ops.

Based on the needs for my case, which is to be able to search based on tags and sorted by timestamp, what is the best way - in terms of optimized search performance - to create the indexes? I'd appreciate if an expert could give me the SQL to create the index and some examples to query for data.

Thanks!

Create the indexes then run your query using explain (analyze, buffers, timing) and check the execution plan — user330315
– user330315, Commented Jan 26, 2018 at 7:38

Vao Tsun · Accepted Answer · 2018-01-29 11:43:35Z

2

http://dbfiddle.uk/?rdbms=postgres_10&fiddle=cbb2336edf796f9b9458be64c4654394

as you can see effective jsonb_ops on small part of jsonb is close to jsonb_path_ops. Close sizes, close timing, only different operators supported. It would start differ very much if you

CREATE INDEX idxgintags ON api USING GIN (jdoc);

answered Jan 29, 2018 at 11:43

Vao Tsun

52.5k13 gold badges114 silver badges149 bronze badges

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

Collectives™ on Stack Overflow

PostgreSQL jsonb indexing for optimized search performance

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