Postgres does not use index on gigantic numeric value

Nothing beats a Saturday morning when you wake up fresh and excited, ready for the second sleep, and realize that your Postgres database was harassed over the night and accumulated a number of downtime minutes that is too embarrassing to state here.

My database load looked like this during the outage.

buffer_mapping looks new. Postgres official documentation on this matter seems to be written by Captain Obvious: Waiting to associate a data block with a buffer in the buffer pool. Thanks for nothing. Basically buffer_mapping is a lightweight lock in read operations, my processes were fighting to reserve buffers in which to read data pages.

I have a read problem.

This query accommodates the highest number of locks:

pp_pqsql_prod=> explain select * FROM "big_table" WHERE "big_table"."id" = 9200190224054041915721;
                                           QUERY PLAN
------------------------------------------------------------------------------------------------
 Gather  (cost=1000.00..4568847.85 rows=535675 width=1673)
   Workers Planned: 2
   ->  Parallel Seq Scan on big_table  (cost=0.00..4514280.35 rows=223198 width=1673)
         Filter: ((id)::numeric = '9200190224054041915721'::numeric)
(4 rows)

It is a sequence scan, on a 100M-row table, so it is obvious why it caused all the ruckus. What's less obviously is why Postgres performed a sequence scan on a primary key column.

With the help of a colleague, it appeared that given a smaller numeric value, the index kicks in just fine.

pp_pqsql_prod=> explain select * FROM "big_table" WHERE "big_table"."id" = 9200190;
                                              QUERY PLAN
-------------------------------------------------------------------------------------------------------
 Index Scan using big_table_pkey on big_table  (cost=0.57..8.59 rows=1 width=1673)
   Index Cond: (id = 9200190)
(2 rows)

The problem is the size of the queried value. Eventually I stumbled upon this stackexchange Q&A. It can be seen in the first explain that because 9200190224054041915721 was too big, it had to be casted into numeric data type. My primary key was not that big, its data type was bigint. So it had to be casted too, because apple can't be compared to orange. What I have now is a numeric to numeric comparison, and a bigint index can't serve that.

Problem be gone and so was my morning.