當值是子查詢或包裝查詢的結果時，不使用索引

我有一個簡單的一對多關係：accounts並且eventsanaccount可能有很多events.

帳戶

1. 標識：uuid
2. …更多不相關的領域

事件

1. id：序列號
2. 類型：列舉$$ ‘activated’, ‘deactivated’, ‘removed’, …more $$
3. account_id : uuid
4. created_at：行的創建日期

事件表索引：

create index "IDX_d82e2f903c778153e7781f455e" on bank_account_events (type, bank_account_id);

我有一個簡單的查詢，它確定特定帳戶的最後一個類型事件何時發生：

explain select created_at
from bank_account_events
where bank_account_id = 'b12edcab-9ac5-4a09-a84c-475c8a73c964'
 and type in ('activated', 'deactivated', 'removed')
order by id desc
limit 1;

查詢的結果表明我們使用了正確的索引：

Limit  (cost=339.51..339.51 rows=1 width=16)
 ->  Sort  (cost=339.51..339.74 rows=94 width=16)
       Sort Key: id DESC
"        ->  Index Scan using ""IDX_d82e2f903c778153e7781f455e"" on bank_account_events  (cost=0.56..339.04 rows=94 width=16)"
"              Index Cond: ((type = ANY ('{activated,deactivated,removed}'::bank_account_events_type_enum[])) AND (bank_account_id = 'b12edcab-9ac5-4a09-a84c-475c8a73c964'::uuid))

但是，當我“包裝”這個查詢並將其用作子查詢時，索引沒有被使用：

explain select ba.id,
 (
   select created_at
   from bank_account_events
   where bank_account_id = ba.id
     and type in ('activated', 'deactivated', 'removed')
   order by id desc
   limit 1
 ) status_date
from bank_accounts ba
where ba.id = 'b12edcab-9ac5-4a09-a84c-475c8a73c964';

上面查詢的結果是：

Index Only Scan using ""PK_5a7a02c20412299d198e097a8fe"" on bank_accounts ba  (cost=0.27..8.43 rows=1 width=24)"
 Index Cond: (id = 'b12edcab-9ac5-4a09-a84c-475c8a73c964'::uuid)
 SubPlan 1
   ->  Limit  (cost=0.43..4.14 rows=1 width=16)
"          ->  Index Scan Backward using ""PK_dddc8f2295ddc2561044644a05a"" on bank_account_events  (cost=0.43..264940.10 rows=71513 width=16)"
"                Filter: ((bank_account_id = ba.id) AND (type = ANY ('{activated,deactivated,removed}'::bank_account_events_type_enum[])))

如您所見，它現在使用的是表 ( PK_dddc8f2295ddc2561044644a05a) 的 PK，它只是 serial id。然後它過濾結果。由於該表有幾百萬行，這非常緩慢。

為什麼 Postgres 在這種情況下不使用索引？唯一的變化是我們不再直接引用 id，而是將其稱為ba.id.

在第一種情況下，它可以在統計資訊中查看它認為有多少條記錄具有 bank_account_id ‘b12edcab-9ac5-4a09-a84c-475c8a73c964’ 並認為在將其與94 結合後type in ...，將有 94 條記錄，而在第二種情況下它不能這樣做，因為在計劃查詢時該特定值是未知的。相反，它認為將有 71513 具有給定的銀行帳戶 IDtype in ...和一些未知的時間值。

顯然，您的銀行帳戶中的事件數量非常不均勻；並且計劃者在將其用作子查詢時不知道您查詢的是哪種類型的帳戶。

更改索引中列的順序以支持對 bank_account_id 的 B 樹搜尋和按類型過濾。過濾後，所有記錄都按 id 排序讀取。通過最後包含 created_at 列，您在索引中擁有了查詢所需的所有數據，並且它根本不會訪問該表。

CREATE INDEX "nc_bank_account_events_bank_account_id"
ON bank_account_events (bank_account_id, type, id, created_at);

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