Issue with GIN Index large data set

Viewed 162

I am doing an experiment on GIN Index for Jsonb type data. till I am having 500K rows in a table, GIN Index is working, however when I increase the data to 50Million rows in a table. The GIN Index gets created however explain analysis doesn't seem to consider the Indexes working.

postgres=# \d users
                              Table "public.users"
  Column   |  Type   | Collation | Nullable |              Default
-----------+---------+-----------+----------+------------------------------------
 _id       | integer |           | not null | nextval('users__id_seq'::regclass)
 id        | integer |           | not null |
 attribute | integer |           | not null |
 value     | jsonb   |           | not null |
Indexes:
    "users_pkey" PRIMARY KEY, btree (_id)
    "user_value_gin" gin (value)
Foreign-key constraints:
    "users_attribute_fkey" FOREIGN KEY (attribute) REFERENCES attribute(id)

=======================================

postgres=# explain analyze select * from users where value @> '{"v": "Rahul"}'::jsonb;
                                                             QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------
 Gather  (cost=1000.00..1017776.57 rows=897059 width=70) (actual time=880.003..23596.085 rows=913446 loops=1)
   Workers Planned: 2
   Workers Launched: 2
   ->  Parallel Seq Scan on users  (cost=0.00..927070.67 rows=373775 width=70) (actual time=451.196..22468.458 rows=304482 loops=3)
         Filter: (value @> '{"v": "Rahul"}'::jsonb)
         Rows Removed by Filter: 16362185
 Planning Time: 105.326 ms
 JIT:
   Functions: 6
   Options: Inlining true, Optimization true, Expressions true, Deforming true
   Timing: Generation 502.947 ms, Inlining 676.053 ms, Optimization 560.960 ms, Emission 94.573 ms, Total 1834.533 ms
 Execution Time: 24099.549 ms
(12 rows)

My question is that is there any limitation Postgres has on GIN Index?

if yes what is the possible resolution/design change is appropriate to overcome this problem if my requirement is to have approximately 50Billion rows in a table.

Regards Praveen

1 Answers

You are retrieving nearly 2% of the table. It is not obvious that using an index is the fastest way to do that.

If you want to force it to use the index, you can set enable_seqscan=off and it will probably use the index then

Or, maybe you can pick a more selective thing to search on if all you want to do is see the index work 'on is own'.

Related