適用範圍：
適用於 PostgreSQL 的 Azure 資料庫 - 彈性伺服器
適用於 PostgreSQL 的 Azure 資料庫彈性伺服器中的 [查詢存放區] 功能會提供方法來追蹤一段時間的查詢效能。 查詢存放區可協助您快速找到執行時間最長又最耗資源的查詢，藉此簡化效能問題疑難排解。 查詢存放區會自動擷取查詢的歷程記錄和執行階段統計資料，並予以保留以供您檢閱。 它會依時間切分資料，以便您查看時態性使用模式。 所有使用者、資料庫和查詢的資料都會儲存在適用於 PostgreSQL 的 Azure 資料庫彈性伺服器執行個體中名為 azure_sys 的資料庫。
啟用查詢存放區
查詢存放區不需額外費用即可供使用。 其為加入功能，因此預設不會在伺服器上予以啟用。 查詢存放區可以針對給定伺服器上的所有資料庫全域予以啟用或停用，但無法根據每個資料庫予以開啟或關閉。
重要事項
請勿在可高載定價層上啟用查詢存放區，因為這會造成效能影響。
在 Azure 入口網站中啟用查詢存放區
- 登入 Azure 入口網站，然後選取適用於 PostgreSQL 的 Azure 資料庫彈性伺服器執行個體。
- 在功能表的 [設定] 區段中，選取 [伺服器參數]。
- 搜尋
pg_qs.query_capture_mode參數。 - 根據您想要追蹤最上層查詢還是巢狀查詢 (在函式或程序內執行的查詢)，將值設定為
top或all，然後選取 [儲存]。 請等候 20 分鐘，以讓第一批資料保存在azure_sys資料庫中。
啟用查詢存放區等候取樣
- 搜尋
pgms_wait_sampling.query_capture_mode參數。 - 將值設定為
all，然後選取 [儲存]。
查詢存放區中的資訊
查詢存放區包含兩個存放區：
- 執行階段統計資料存放區，用於保存查詢執行統計資料資訊。
- 等候統計資料存放區，用於保存等候統計資料資訊。
使用查詢存放區的常見案例包括：
- 判斷查詢在給定時間範圍內的執行次數。
- 跨時間範圍比較查詢的平均值行時間，查看大幅變化。
- 識別過去幾小時中執行最久的查詢。
- 識別前 N 項等候資源的查詢。
- 了解等候特定查詢的本質。
為了讓空間使用量降到最低，會經過一段固定且可設定的時間範圍，才彙總執行階段統計資料存放區的執行階段執行統計資料。 這些存放區中的資訊可以使用檢視來查詢。
存取查詢存放區資訊
查詢存放區資料會儲存在適用於 PostgreSQL 的 Azure 資料庫彈性伺服器執行個體的 azure_sys 資料庫中。
下列查詢會傳回記錄在查詢存放區中的查詢相關資訊：
SELECT * FROM query_store.qs_view;
此查詢會傳回等候統計資料的相關資訊：
SELECT * FROM query_store.pgms_wait_sampling_view;
尋找等候查詢
等候事件類型會依相似性，將不同的等候事件結合成貯體。 查詢存放區提供等候事件類型、特定的等候事件名稱，以及有問題的查詢。 能夠將此等候資訊與查詢執行階段相互關聯，表示您可以更深入了解查詢效能特性從何而來。
以下這些範例說明如何使用查詢存放區中的等候統計資料深入了解您的工作負載：
| 觀測 | 動作 |
|---|---|
| 高鎖定等候數 | 查看受影響查詢的查詢文字，並找出目標實體。 在查詢存放區中尋找其他查詢，這些查詢經常執行且/或持續時間很長，並且會修改相同的實體。 找出這些查詢之後，請考慮變更應用程式邏輯，改善並行存取，或使用限制較少的隔離等級。 |
| 高緩衝區 IO 等候數 | 在查詢存放區中尋找實體讀取次數高的查詢。 如果其符合高 IO 等候的查詢，請考慮啟用自動化索引微調功能，以查看其是否可以建議建立一些索引，這可能會減少這些查詢的實體讀取數目。 |
| 高記憶體等候數 | 找出查詢存放區中記憶體耗用量名列前茅的查詢。 這些查詢可能會進一步延遲受影響查詢的進度。 |
設定選項
啟用查詢存放區時，其會以 pg_qs.interval_length_minutes 伺服器參數所決定的彙總時間範圍長度 (預設為 15 分鐘) 來儲存資料。 針對每個時間範圍，其每個時間範圍最多會儲存 500 個相異查詢。 區分每個查詢唯一性的屬性為 user_id (執行查詢使用者的識別碼)、db_id (查詢執行內容資料庫的識別碼)，以及 query_id (可唯一識別所執行查詢的整數值)。 如果在設定的間隔期間，相異查詢的數目達到 500 個，則會解除配置 5% 的所記錄查詢，以騰出更多空間。 首先解除配置的就是執行次數最少的查詢。
下列選項適用於設定查詢存放區參數：
| 參數 | 說明 | 預設值 | 範圍 |
|---|---|---|---|
pg_qs.interval_length_minutes (*) |
查詢存放區的擷取間隔 (以分鐘為單位)。 定義資料持續性的頻率。 | 15 |
1 - 30 |
pg_qs.is_enabled_fs |
僅限內部使用：此參數會當作功能覆寫參數使用。 如果顯示為關閉，則會停用查詢存放區，儘管已設定 pg_qs.query_capture_mode 的值。 |
on |
on、off |
pg_qs.max_plan_size |
查詢存放區從查詢計劃文字儲存的最大位元組數目；會截斷較長的計劃。 | 7500 |
100 - 10000 |
pg_qs.max_query_text_length |
可儲存的查詢文字長度上限；會截斷過久的查詢。 | 6000 |
100 - 10000 |
pg_qs.parameters_capture_mode |
是否及何時擷取查詢位置參數。 | capture_parameterless_only |
capture_parameterless_only、capture_first_sample |
pg_qs.query_capture_mode |
要追蹤的陳述式。 | none |
none、top、all |
pg_qs.retention_period_in_days |
查詢存放區的保留期間範圍 (以天為單位)。 會自動刪除較舊的資料。 | 7 |
1 - 30 |
pg_qs.store_query_plans |
查詢計劃是否應儲存在查詢存放區中。 | off |
on、off |
pg_qs.track_utility |
查詢存放區是否必須追蹤公用程式命令。 | on |
on、off |
(*) 靜態伺服器參數，其需要重新啟動伺服器，其值的變更才會生效。
附註
如果您變更 pg_qs.max_query_text_length 參數的值，在進行變更之前擷取的所有查詢文字會繼續使用相同的 query_id 和 sql_query_text。 其可能會造成新值不會生效的印象，但對於之前在查詢存放區中未記錄的查詢，您會看到查詢文字使用新設定的最大長度。 這依設計而定，且會在檢視和函式中說明。 如果您執行 query_store.qs_reset，其會移除查詢存放區目前為止所記錄的所有資訊，包括針對每個查詢識別碼擷取的文字，如果再次執行這些查詢，則新設定的最大長度會套用至所擷取的文字。
下列選項特別適用於等候統計資料：
| 參數 | 說明 | 預設值 | 範圍 |
|---|---|---|---|
pgms_wait_sampling.history_period |
等候事件取樣頻率 (以毫秒為單位)。 | 100 |
1 - 600000 |
pgms_wait_sampling.is_enabled_fs |
僅限內部使用：此參數會當作功能覆寫參數使用。 如果顯示為 off，則會停用等候取樣，儘管已設定 pgms_wait_sampling.query_capture_mode 的值也一樣。 |
on |
on、off |
pgms_wait_sampling.query_capture_mode |
pgms_wait_sampling 延伸模組必須追蹤的陳述式。 |
none |
none、all |
附註
pg_qs.query_capture_mode 取代 pgms_wait_sampling.query_capture_mode。 如果 pg_qs.query_capture_mode 為 none，則 pgms_wait_sampling.query_capture_mode 設定無效。
使用 Azure 入口網站為參數取得或設定不同的值。
檢視和函式
您可以查詢查詢存放區所記錄的資訊，或使用 query_store 資料庫的 azure_sys 結構描述中提供的一些檢視和函式加以刪除。 PostgreSQL 公用角色中的任何人都可以使用這些檢視，查看查詢存放區中的資料。 這些檢視僅適用於 azure_sys 資料庫。
查詢的正規化方式是查看其結構，並忽略任何語意不重要的項目，例如常值、常數、別名或大小寫差異。
如果兩個查詢的語意相同，則即使它們針對相同的參考資料行和資料表使用不同的別名，還是會以相同的 query_id 予以識別。 如果兩個查詢只有使用的常值不同，則也會以相同的 query_id 予以識別。 針對以相同的 query_id 識別的查詢，其 sql_query_text 會是下列時間之後第一個所執行查詢的內容：自查詢存放區開始記錄活動之後，或自上次因已執行 query_store.qs_reset 函式而捨棄持續保存的資料之後。
查詢正規化的運作方式
以下是嘗試說明此正規化運作方式的一些範例：
假設您使用下列陳述式來建立資料表：
create table tableOne (columnOne int, columnTwo int);
您會啟用查詢存放區資料收集，而且單一或多個使用者會依此確切順序來執行下列查詢：
select * from tableOne;
select columnOne, columnTwo from tableOne;
select columnOne as c1, columnTwo as c2 from tableOne as t1;
select columnOne as "column one", columnTwo as "column two" from tableOne as "table one";
所有先前的查詢都會共用相同的 query_id。 而且，查詢存放區所保留的文字就是啟用資料收集之後第一個所執行查詢的文字。 因此，其會是 select * from tableOne;。
下列一組查詢在正規化之後就會與先前的一組查詢不符，因為 WHERE 子句會使其語意不同：
select columnOne as c1, columnTwo as c2 from tableOne as t1 where columnOne = 1 and columnTwo = 1;
select * from tableOne where columnOne = -3 and columnTwo = -3;
select columnOne, columnTwo from tableOne where columnOne = '5' and columnTwo = '5';
select columnOne as "column one", columnTwo as "column two" from tableOne as "table one" where columnOne = 7 and columnTwo = 7;
不過，這個最後一組中的所有查詢都會共用相同的 query_id，而且用來識別這些查詢的文字全都是批次 select columnOne as c1, columnTwo as c2 from tableOne as t1 where columnOne = 1 and columnTwo = 1; 中第一個查詢的文字。
最後，在下方尋找一些不符合上一個批次中 query_id 查詢的查詢和其不相符的原因：
查詢：
select columnTwo as c2, columnOne as c1 from tableOne as t1 where columnOne = 1 and columnTwo = 1;
不相符的原因：資料行清單指的是相同的兩個資料行 (columnOne 和 ColumnTwo)，但其參考的順序相反：從上一個批次中的 columnOne, ColumnTwo 到此查詢中的 ColumnTwo, columnOne。
查詢：
select * from tableOne where columnTwo = 25 and columnOne = 25;
不相符的原因：WHERE 子句中所評估運算式的參照順序會從上一個批次中的 columnOne = ? and ColumnTwo = ? 反轉為此查詢中的 ColumnTwo = ? and columnOne = ?。
查詢：
select abs(columnOne), columnTwo from tableOne where columnOne = 12 and columnTwo = 21;
不相符的原因：資料行清單中的第一個運算式已不再是 columnOne，而是透過 abs 評估的 columnOne 函式 (abs(columnOne))，這在語意上並不相等。
查詢：
select columnOne as "column one", columnTwo as "column two" from tableOne as "table one" where columnOne = ceiling(16) and columnTwo = 16;
不相符的原因：WHERE 子句中的第一個運算式不會再評估 columnOne 與常值相等，而是透過常值評估的 ceiling 函式結果，這在語意上並不相等。
檢視
query_store.qs_view
此檢視會傳回在查詢存放區支援資料表中持續保存的所有資料。 除非時間範圍結束，否則不會顯示目前使用中時間範圍仍要在記憶體內部記錄的資料，而且會收集其記憶體內部動態資料，並將其持續保存至磁碟上所儲存的資料表。 此檢視會針對每個相異資料庫 (db_id)、使用者 (user_id) 和查詢 (query_id) 傳回不同的資料列。
| 名稱 | 型別 | 參考 | 說明 |
|---|---|---|---|
runtime_stats_entry_id |
BIGINT | 來自 runtime_stats_entries 資料表的識別碼。 | |
user_id |
oid | pg_authid.oid | 已執行陳述式的使用者物件識別 (OID)。 |
db_id |
oid | pg_database.oid | 在其中執行陳述式的資料庫物件識別 (OID)。 |
query_id |
BIGINT | 從陳述式的剖析樹狀結構計算的內部雜湊碼。 | |
query_sql_text |
varchar（10000） | 代表性陳述式的文字。 結構相同的不同查詢會群集在一起；此文字就式叢集中第一個查詢的文字。 查詢文字長度上限的預設值為 6000，而且可以使用查詢存放區參數 pg_qs.max_query_text_length 進行修改。 如果查詢的文字超過這個最大值，則會將其截斷為前 pg_qs.max_query_text_length 個位元組。 |
|
plan_id |
BIGINT | 對應至此查詢的計畫識別碼。 | |
start_time |
時間戳 | 會依時間範圍彙總查詢。 伺服器參數 pg_qs.interval_length_minutes 會定義這些視窗的時間範圍 (預設值為 15 分鐘)。 此資料行會對應至記錄此項目的視窗開始時間。 |
|
end_time |
時間戳 | 對應至此項目時間範圍的結束時間。 | |
calls |
BIGINT | 此時間範圍中的查詢執行次數。 請注意，針對平行查詢，可驅動查詢執行的後端處理序的每個執行呼叫數目都會對應至 1，再加上每個後端背景工作處理序 (啟動以對可執行執行樹狀結構的平行分支進行共同作業) 的其他許多單位。 | |
total_time |
雙精度 | 查詢總執行時間 (以毫秒為單位)。 | |
min_time |
雙精度 | 查詢最短執行時間 (以毫秒為單位)。 | |
max_time |
雙精度 | 查詢最長執行時間 (以毫秒為單位)。 | |
mean_time |
雙精度 | 查詢平均執行時間 (以毫秒為單位)。 | |
stddev_time |
雙精度 | 查詢執行時間標準差 (以毫秒為單位)。 | |
rows |
BIGINT | 陳述式所擷取或影響的資料列總數。 請注意，針對平行查詢，每個執行的資料列數目都會對應至可驅動查詢執行的後端處理序傳回給用戶端的資料列數目，再加上每個後端背景工作處理序 (啟動以對可執行執行樹狀結構的平行分支進行共同作業) 傳回給驅動執行查詢的後端處理序的所有資料列總和。 | |
shared_blks_hit |
BIGINT | 陳述式的共用區塊快取點擊總數。 | |
shared_blks_read |
BIGINT | 陳述式所讀取的共用區塊總數。 | |
shared_blks_dirtied |
BIGINT | 陳述式所變動的共用區塊總數。 | |
shared_blks_written |
BIGINT | 陳述式所寫入的共用區塊總數。 | |
local_blks_hit |
BIGINT | 陳述式的本機區塊快取點擊總數。 | |
local_blks_read |
BIGINT | 陳述式所讀取的本機區塊總數。 | |
local_blks_dirtied |
BIGINT | 陳述式所變動的本機區塊總數。 | |
local_blks_written |
BIGINT | 陳述式所寫入的本機區塊總數。 | |
temp_blks_read |
BIGINT | 陳述式所讀取的暫存區塊總數。 | |
temp_blks_written |
BIGINT | 陳述式所寫入的暫存區塊總數。 | |
blk_read_time |
雙精度 | 陳述式讀取區塊所花費的總時間 (以毫秒為單位) (如果啟用 track_io_timing，否則為零)。 | |
blk_write_time |
雙精度 | 陳述式寫入區塊所花費的總時間 (以毫秒為單位) (如果啟用 track_io_timing，否則為零)。 | |
is_system_query |
布林值 | 判斷具有user_id = 10 (azuresu) 的角色是否執行查詢。 該使用者具有超級使用者權限，並用來執行控制平面作業。 因為此服務是受控 PaaS 服務，所以只有 Microsoft 是該超級使用者角色的一部分。 | |
query_type |
文字 | 查詢所代表的作業類型。 可能的值為 unknown、select、update、insert、delete、merge、utility、nothing、undefined。 |
|
search_path |
text | 擷取查詢時所設定的 search_path 值。 | |
query_parameters |
text | JSON 物件的文字表示，其值會傳遞至參數化查詢的位置參數。 此資料行只會在兩種情況下填入其值：1) 表示非參數化查詢。 2) 針對參數化查詢，當 pg_qs.parameters_capture_mode 設為 capture_first_sample 時，以及在查詢存放區可以在執行時間擷取查詢參數值的情況下。 |
|
parameters_capture_status |
文字 | 查詢所代表的作業類型。 可能的值為 succeeded (查詢未參數化，或是已成功擷取參數化查詢和值)、disabled (查詢已參數化，但未擷取參數，因為 pg_qs.parameters_capture_mode 已設定為 capture_parameterless_only)，too_long_to_capture (查詢已參數化，但未擷取參數，因為產生的 JSON 長度會在此檢視的 query_parameters 資料行中浮出水面，因此會視為時間過長而查詢存放區無法保存)，too_many_to_capture (查詢已參數化，但未擷取參數，因為參數總數視為過度，而查詢存放區無法保存)，serialization_failed (查詢已參數化，但至少其中一個做為參數傳遞的值無法序列化為文字)。 |
query_store.query_texts_view
此檢視會傳回查詢存放區中的查詢文字資料。 每個相異 query_sql_text 都有一個資料列。
| 名稱 | 型別 | 說明 |
|---|---|---|
query_text_id |
BIGINT | query_texts 資料表識別碼 |
query_sql_text |
varchar（10000） | 代表性陳述式的文字。 結構相同的不同查詢會群集在一起；此文字就式叢集中第一個查詢的文字。 |
query_type |
SMALLINT | 查詢所代表的作業類型。 在 PostgreSQL <= 14 的版本中，可能的值為 0 (未知)、1 (選取)、2 (更新)、3 (插入)、4 (刪除)、5 (公用程式)、6 (無)。 在 PostgreSQL >= 15 的版本中，可能的值為 0 (未知)、1 (選取)、2 (更新)、3 (插入)、4 (刪除)、5 (合併)、6 (公用程式)、7 (無)。 |
query_store.pgms_wait_sampling_view
此檢視會傳回查詢存放區中的等候事件資料。 此檢視會針對每個相異資料庫 (db_id)、使用者 (user_id)、查詢 (query_id) 和事件 (event) 傳回不同的資料列。
| 名稱 | 型別 | 參考 | 說明 |
|---|---|---|---|
start_time |
時間戳 | 會依時間範圍彙總查詢。 伺服器參數 pg_qs.interval_length_minutes 會定義這些視窗的時間範圍 (預設值為 15 分鐘)。 此資料行會對應至記錄此項目的視窗開始時間。 |
|
end_time |
時間戳 | 對應至此項目時間範圍的結束時間。 | |
user_id |
oid | pg_authid.oid | 執行陳述式使用者的物件識別碼。 |
db_id |
oid | pg_database.oid | 執行陳述式資料庫的物件識別碼。 |
query_id |
BIGINT | 從陳述式的剖析樹狀結構計算的內部雜湊碼。 | |
event_type |
文字 | 後端正在等候的事件類型。 | |
event |
文字 | 如果後端目前正在等候，則是等候事件名稱。 | |
calls |
整數 | 擷取相同事件的次數。 |
附註
如需 event_type 檢視的 event 和 query_store.pgms_wait_sampling_view 資料行中的可能值清單，請參閱 pg_stat_activity 的官方文件，並尋找參考同名資料行的資訊。
query_store.query_plans_view
此檢視會傳回用來執行查詢的查詢計劃。 每個相異資料庫識別碼和查詢識別碼都會各有一個資料列。 查詢存放區只會記錄非使用量查詢的查詢計劃。
| 名稱 | 型別 | 參考 | 說明 |
|---|---|---|---|
plan_id |
BIGINT | 來自 EXPLAIN 所產生正規化查詢計畫的雜湊值。 其格式為正規化，因為會排除計畫節點的估計成本，以及緩衝區的使用方式。 | |
db_id |
oid | pg_database.oid | 在其中執行陳述式的資料庫物件識別 (OID)。 |
query_id |
BIGINT | 從陳述式的剖析樹狀結構計算的內部雜湊碼。 | |
plan_text |
varchar（10000） | 陳述式在 costs=false、buffers=false 且 format=text 時的執行計畫。 與 EXPLAIN 所產生的輸出完全相同。 |
函式
query_store.qs_reset
此函數會捨棄查詢存放區至今收集到的所有統計資料。 其會捨棄已關閉時間範圍的統計資料，這些統計資料已保存至磁碟上的資料表。 其也會捨棄目前時間範圍的統計資料，該時間範圍只存在於記憶體內部。 只有伺服器管理員角色 (azure_pg_admin) 的成員才能執行此函式。
query_store.staging_data_reset
此函式會捨棄查詢存放區收集到記憶體內部的所有統計資料 (即，記憶體中的資料尚未排清至支援查詢存放區持續保存所收集資料的磁碟上資料表)。 只有伺服器管理員角色 (azure_pg_admin) 的成員才能執行此函式。
唯讀模式
當 Azure Database for PostgreSQL 的彈性伺服器處於只讀模式時，例如當 default_transaction_read_only 參數設為 on 時，或因為達到儲存容量而自動啟用只讀模式時，查詢存放區不會記錄任何資料。
在具有讀取複本的伺服器上啟用查詢存放區，並不會自動在任何讀取複本上啟用查詢存放區。 即使您在任何讀取複本加以啟用，查詢存放區也不會記錄在任何讀取複本上執行的查詢，因為其會在只讀模式中運作，直到您將查詢升階為主要複本為止。