On 9/20/21 12:52 PM, ramikvl@xxxxxxxxx wrote:
Hello Julien,
On 9/17/21 4:00 PM, Julien Rouhaud wrote:
Hi,
On Fri, Sep 17, 2021 at 9:55 PM <ramikvl@xxxxxxxxx> wrote:
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?
Yes, because doing an index scan on a table containing a single row is
an order or magnitude less efficient than simply doing a sequential
scan. You should try to simulate something close to your production
data to see something interesting.
Thank you for the tip. I've tried to generate more data. I have 2000
rows in the table but the query still uses sequential scan.
Seq Scan on api (cost=0.00..131.00 rows=2000 width=64) (actual
time=0.005..0.959 rows=2000 loops=1)
Filter: ((jdoc -> 'tags'::text) ? 'qui'::text)
Planning Time: 0.064 ms
Execution Time: 1.027 ms
Any thoughts?
Strangely enough when I re-created the index it's working, now. I
probably made a mistake.
Thank you.
