Postgres 9.6.8, a table with 6GB including a jsonb field that is up to 15MB. The query planner is not using any indices btree or gin when querying that table. When I add the same indices to a local/dev table (not as big), indices are used.

api=> d api.commands
                            Table "api.commands"
        Column        |            Type             | Collation | Nullable | Default 
----------------------+-----------------------------+-----------+----------+---------
 id                   | uuid                        |           | not null | 
 error                | boolean                     |           |          | false
 command              | jsonb                       |           |          | 
 program_id           | uuid                        |           |          | 
 account_id           | uuid                        |           |          | 
Indexes:
    "commands_pkey" PRIMARY KEY, btree (id)
    "idx_cmd_account" btree (account_id)
    "idx_cmd_program" btree (program_id)
    "ix_cmd_created" btree (created)
    "idx_cmd_gin" gin (command jsonb_path_ops)
    "idx_cmd_type" btree ((((command -> 'command'::text) -> 'meta'::text) ->> 'type'::text))

Given this table with listed indices, I’m trying to query for a specific "type" of command e.g.

select * from api.commands where command->'command'->'meta'->>'type' = 'create'

query planner returns

api=> explain select * from api.commands where command->'command'->'meta'->>'type' = 'create';
                                           QUERY PLAN                                           
------------------------------------------------------------------------------------------------
 Seq Scan on commands  (cost=0.00..63589.26 rows=604 width=739)
   Filter: ((((command -> 'command'::text) -> 'meta'::text) ->> 'type'::text) = 'create'::text)
(2 rows)

I’ve also tried to use the GIN index with

api=> explain select * from api.commands where command->'command'->'meta' @> '{ "type": "create" }';
                                                       QUERY PLAN                                                       
------------------------------------------------------------------------------------------------------------------------
 Seq Scan on command_registry  (cost=0.00..63287.23 rows=121 width=456) (actual time=50.802..333627.363 rows=2 loops=1)
   Filter: (((command -> 'command'::text) -> 'meta'::text) @> '{"type": "create"}'::jsonb)
   Rows Removed by Filter: 120921
   Buffers: shared hit=218390 read=131857
 Planning time: 115.912 ms
 Execution time: 333627.427 ms
(6 rows)

I tried to disable seqscan, and that just increases the cost. Further, even if I don’t include the large JSONB field in the query no index is used:

api=> explain select * from api.commands where program_id = '620D00ED-5368-4803-928E-30D454F4AFD3';
                              QUERY PLAN                               
-----------------------------------------------------------------------
 Seq Scan on commands  (cost=0.00..62683.16 rows=34 width=739)
   Filter: (program_id = '620d00ed-5368-4803-928e-30d454f4afd3'::uuid)
(2 rows)

All of the above queries are using the index on a local database with less data. I tried vacuuming the big database to no avail.

Can anyone explain what’s going on?

---

Update

I resized my database (running in RDS) to match the production sized database and the planner is finding the btree indices. Still not the GIN index, and I’ve updated the plan to include the execution & buffer usage.