如何優化這個 IN 查詢？

可以說我有這個架構

Resource(resource_mapping_id:Uuid,
reaource_id: uuid,
node_id: varchar,
date: date,
available: boolean,
resource_start: varchar,
resource_end: varchar)

所以我在上面形成了複合鍵(resource_mapping_id, resource_id, node_id, date, resource_start, resource_end)

注意：節點也是 uuid 儲存為文本。現在我有這兩個查詢：

update resource set available = :value where resource_id=:somevalue and date=:somedate and resource_start=:sometime and resource_end=:sometime

和

select * from resource where resource_id In (:resourceidlist) and date in (:dates) and node_id in (:nodeIds)

這張表包含大量的記錄，你可以說大約 5 億左右..

因此，每當我通過 jpa 通過我的 Java 應用程序訪問這些查詢時，它們都會使數據庫的 CPU 使用率飆升至 100%。

所以在做了分析之後，我創建了一個索引說

Index(resource_id, node_id, date)

這反過來又解決了更新查詢的問題，即使它在並行執行緒中執行，CPU 也從未出現過一點點飆升。

但是現在來到 select 語句，當參數變高時我遇到了問題。所以我對它們進行了批量處理，我的意思是批量 x 沒有。可以處理節點 ID、資源 ID 和日期的數量，即使使用 100（注意，所有參數的大小相同，如果我 tel 100，總共 300）參數，它會加速 CPU 和其他執行緒進入等待狀態！

如何解決這個問題？我應該改變我的查詢還是什麼？我是否應該僅針對這種情況進行任何其他更改或創建進一步的索引？請幫我。

我正在使用 postgres v13。

為了支持該SELECT陳述，您首先需要確定三個條件中的哪一個是選擇性的，即過濾掉大量行。對於每個選擇性條件，只在該列上創建一個 B 樹索引，這樣您最終會得到最多三個單列索引。

PostgreSQL 可以結合這些索引來加快查詢速度。在這種情況下，多列索引不是很有用。

對於UPDATE，子句中出現的所有列的多列索引WHERE將是完美的。但是 PostgreSQL 也可以使用我們上面創建的單列索引。

總有一個權衡：索引可以加快查詢速度，但會減慢數據修改速度。所以你不想擁有太多。

在 where 子句序列中創建表索引並嘗試它。例如。

where resource_id In (:resourceidlist) and date in (:dates) and
node_id in (:nodeIds)

表索引如

Table Index(resource_id, date , node_id )

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