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?
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.
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
Get help from others!
Recent Answers
Recent Questions
© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP