Thread

1. jsonb Indexing

   ramikvl@gmail.com — 2021-09-17T13:55:29Z

   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
   
   
   
   
   

2. Re: jsonb Indexing

   Julien Rouhaud <rjuju123@gmail.com> — 2021-09-17T14:00:15Z

   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.
   
   
   
   

3. Re: jsonb Indexing

   ramikvl@gmail.com — 2021-09-20T10:52:54Z

   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?
   
   
   
   
   

4. Re: jsonb Indexing

   Ilya Anfimov <ilan@tzirechnoy.com> — 2021-09-20T11:24:43Z

   On Mon, Sep 20, 2021 at 12:52:54PM +0200, ramikvl@gmail.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@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?
   
    The planner expects index selectivity around 1 (all the rows to be selected).
    btw, it was right (all the rows were selected).
   
    So, trying to select something by the index is just wasting time,
   compared to seq scan.
   
   > 
   > 
   
   
   
   

5. Re: jsonb Indexing

   ramikvl@gmail.com — 2021-09-20T11:46:07Z

   On 9/20/21 12:52 PM, ramikvl@gmail.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@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?
   
   Strangely enough when I re-created the index it's working, now. I 
   probably made a mistake.
   
   Thank you.