TransWikia.com

Postgres index not being used

Database Administrators Asked on November 14, 2021

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.

One Answer

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.

Answered by Jason on November 14, 2021

Add your own answers!

Ask a Question

Get help from others!

© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP