CODEWITHSUNDEEP
postgresqljsonb
ikevin8me
ikevin8me

Reputation: 4313

PostgreSQL jsonb indexing for optimized search performance

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:

  1. https://www.postgresql.org/docs/current/static/datatype-json.html#JSON-INDEXING
  2. https://www.postgresql.org/docs/current/static/gin-builtin-opclasses.html

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!

Upvotes: 1

Views: 5035

Answers (1)

Vao Tsun
Vao Tsun

Reputation: 51649

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);

Upvotes: 2

Related Questions