PostgreSQL 忽略索引，執行 seq 掃描

我的表包含列的索引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

簡單的查詢用於 seq 掃描

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_balance)::double precision = 'NaN'::double precision)
          Rows Removed by Filter: 389636289
Planning Time: 23.985 ms
Execution Time: 237365.719 ms
(7 rows)

如何讓 PostgreSQL 使用索引？或者換句話說，是否有更有效的方法來掃描表是否存在 NaN 值？

準確的解釋

Postgres 有一個操作符 forfloat8 = float8和 for numeric = numeric。但不是為了numeric = float8。必須強制轉換一個操作數。

double precision，又名float8是數字類型中的“首選”數據類型。請參閱pg_type.typipreferred。

運算符類型解析最終在第3.d段中決定。

遍歷所有候選人並保留那些接受首選類型的候選人

float8 = float8獲勝。您的索引建立在操作符類之上，numeric並且不適用。砰。

解決方案

將double precision查詢中的轉換為替換為更合理的轉換為，與已經建議的類似Stanislavnumeric的類型相匹配。total_balance

或者簡單地使用'NaN'沒有顯式轉換的無類型文字。這會自動解析為total_balance分配中的類型。看：

• 如何避免 PostgreSQL 中的隱式類型轉換？

優化方案

是否有更有效的方法來掃描表是否存在 NaN 值？

如果這是您查詢的重點，那麼部分索引對於您的情況會更有效：

CREATE INDEX balances_total_balance_nan_idx ON balances_snapshots ((true))
WHERE total_balance = 'NaN';

實際的索引表達式幾乎不重要。我用了一個常數。看：

• IS NULL 上的 Postgres 部分索引不起作用

您的EXPLAIN輸出報告接近 4 億行 ( 389636289)，但實際上沒有一個total_balance = 'NaN'( rows=0)。建議的部分索引的最小大小為8 kB 而不是 ~ 10 GB (?) 對於完整索引，幾乎沒有任何寫入成本，並且使查詢幾乎可以立即工作。

（您甚至需要所有其他現有索引嗎？）

WHERE子句中的表達式必須與查詢中使用的表達式匹配。如果您的查詢（？）中的奇怪類型不匹配有充分的理由，請這樣做：

...
WHERE total_balance = float8 'NaN'

旁白：浪費的儲存空間

說到這一點，像這樣重新排序表列將節省相當多 MB 的儲存空間和 RAM：

                                         Table "public.balances_snapshots"
   Column     |            Type             | Collation | Nullable |                    Default
---------------+-----------------------------+-----------+----------+------------------------------------------------
id            | integer                     |           | not null | nextval('balances_snapshots_id_seq'::regclass)
user_id       | integer                     |           |          |
total_balance | numeric                     |           | not null |
asset_id      | text                        |           |          |
timestamp     | timestamp without time zone |           |          | now()

看：

• 在 PostgreSQL 中計算和節省空間

引用自：https://dba.stackexchange.com/questions/305631