From: | ramikvl(at)gmail(dot)com |
---|---|
To: | Julien Rouhaud <rjuju123(at)gmail(dot)com> |
Cc: | "pgsql-generallists(dot)postgresql(dot)org" <pgsql-general(at)lists(dot)postgresql(dot)org> |
Subject: | Re: jsonb Indexing |
Date: | 2021-09-20 11:46:07 |
Message-ID: | ce86c294-29ad-3984-6a78-803133f8b859@gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On 9/20/21 12:52 PM, ramikvl(at)gmail(dot)com wrote:
> Hello Julien,
>
> On 9/17/21 4:00 PM, Julien Rouhaud wrote:
>> Hi,
>>
>> On Fri, Sep 17, 2021 at 9:55 PM <ramikvl(at)gmail(dot)com> 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.
From | Date | Subject | |
---|---|---|---|
Next Message | Abhay Gupta | 2021-09-20 12:58:21 | Azure Postgresql High connection establishment time |
Previous Message | Ilya Anfimov | 2021-09-20 11:24:43 | Re: jsonb Indexing |