Re: jsonb Indexing

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.