Postgresql

如何優化這個 IN 查詢?

  • September 23, 2021

可以說我有這個架構

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