PostgreSQL ignores index, runs seq scan

My table contains an index for column total_balance:

\d balances_snapshots
                                          Table "public.balances_snapshots"
    Column     |            Type             | Collation | Nullable |                    Default
---------------+-----------------------------+-----------+----------+------------------------------------------------
 user_id       | integer                     |           |          |
 asset_id      | text                        |           |          |
 timestamp     | timestamp without time zone |           |          | now()
 total_balance | numeric                     |           | not null |
 id            | integer                     |           | not null | nextval('balances_snapshots_id_seq'::regclass)
Indexes:
    "balances_snapshots_pkey" PRIMARY KEY, btree (id)
    "balances_snapshots_asset_id_idx" btree (asset_id)
    "balances_snapshots_timestamp_idx" btree ("timestamp")
    "balances_snapshots_user_id_idx" btree (user_id)
    "balances_total_balance_idx" btree (total_balance)
Foreign-key constraints:
    "balances_snapshots_asset_id_fkey" FOREIGN KEY (asset_id) REFERENCES assets(id) ON UPDATE CASCADE ON DELETE CASCADE
    "balances_snapshots_user_id_fkey" FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE

And simple query goes for seq scan

```
explain analyze SELECT EXISTS (
SELECT
1
FROM
balances_snapshots
WHERE
total_balance = double precision 'NaN'
LIMIT 1
) as exists;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------
Result (cost=4.75..4.76 rows=1 width=1) (actual time=237365.680..237365.681 rows=1 loops=1)
InitPlan 1 (returns $0)
-> Seq Scan on balances_snapshots (cost=0.00..9257326.32 rows=1948181 width=0) (actual time=237365.675..237365.676 rows=0 loops=1)
Filter: ((total

The problem is that PG has to cast original numeric to double precision:

Filter: ((total_balance)::double precision = ...

So the index that was built contains numeric, but you need double precision - hence the index can't be used. You need to stay away from casting the values in the column:

WHERE
    total_balance = 'NaN'::numeric

PS: it's weird to see NaN in a database. Why not store null?

StackExchange Database Administrators Q#305631, answer score: 10