Re: jsonb Indexing

Hello Julien,

On 9/17/21 4:00 PM, Julien Rouhaud wrote:
> Hi,
>
> On Fri, Sep 17, 2021 at 9:55 PM <ramikvl@gmail.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?