jsonb Indexing

From: ramikvl(at)gmail(dot)com
To: pgsql-general(at)lists(dot)postgresql(dot)org
Subject: jsonb Indexing
Date: 2021-09-17 13:55:29
Message-ID: 3c162c26-e940-e49e-a465-6d484b0ca3a9@gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hello,

I'm new to jsonb feature of postgres. I have PostgreSQL 13.2 running
locally in docker.

I've been playing a little bit with jsonb indexes described
https://www.postgresql.org/docs/13/datatype-json.html#JSON-INDEXING and
I'm not able make the GIN index work on 'tags'.

I was wondering what I'm doing wrong. There are steps what I've tried:

CREATE TABLE api (
    jdoc jsonb
);

INSERT INTO api (jdoc)
    VALUES ('{
    "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,
    "tags": [
        "enim",
        "aliquip",
        "qui"
    ]
}');

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

EXPLAIN ANALYZE SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc ->
'tags' ? 'qui';

And the result is

Seq Scan on api  (cost=0.00..1.02 rows=1 width=64) (actual
time=0.019..0.021 rows=1 loops=1)
  Filter: ((jdoc -> 'tags'::text) ? 'qui'::text)

Planning Time: 0.115 ms

Execution Time: 0.047 ms

Do you know why Index Scan on idxgintag is not used?

Thank you,

Vlasta

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2021-09-17 13:55:56 Re: SELECT FOR UPDATE returns zero rows with CTE
Previous Message Tom Browder 2021-09-17 12:58:03 Re: The tragedy of SQL