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