Postgres index not being used

Question

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.

• user1822: Please edit your question and add the execution plans generated using explain (analyze, buffers) when run against the real table.
• Jason: i resized the database to match my production sized db, and the query planner found the btree indices, but not the gin index. I've updated plan for that one.

Answer 1

This was operator error - I was looking at the RDS console and I noticed the CPU was pegged at 100% after resizing. I did some digging and found some errant processes that were hogging DB resources, after killing and letting the server settle down, all of the indices GIN & BTREE were found.

• Laurenz Albe: That cannot be the reason, except if the CPU overload blocked an autoanalyze.
• Jason: ok, I'm willing to say that's what happened. Possibly not enogh memory on the server to check indices? any ideas on how to confirm?
• Laurenz Albe: Always hard to tell from afar and after the fact.