0

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.

  • Please edit your question and add the execution plans generated using explain (analyze, buffers) when run against the real table. – a_horse_with_no_name Jun 28 at 13:20
  • 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. – Jason Jun 28 at 13:48
0

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.

  • That cannot be the reason, except if the CPU overload blocked an autoanalyze. – Laurenz Albe Jun 28 at 19:25
  • 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? – Jason Jun 29 at 0:45
  • Always hard to tell from afar and after the fact. – Laurenz Albe Jun 29 at 3:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.