# 我所理解的 PostgreSQL 06: 看懂 Query Plan，不再只是 explain analyze 而已

{%hackmd @moment89/tXJIcGqmSI6_fJQp2hnJUA %}

## 改善 SQL 效能的第一步: 看查詢計畫

在資料庫效能調校的領域中，最常見、也最容易被忽略的問題來源，就是 SQL 查詢本身的設計與執行方式。

了解資料庫如何解讀與執行 SQL 語句，是提升效能的關鍵第一步，==任何會向資料庫發送 SQL 的使用者都建議稍微涉略～==

查看 PostgreSQL 針對 SQL 的所做的查詢計畫可以幫助我們檢視資料庫實際採用的查詢策略，用於觀察這段 SQL 查詢的瓶頸點，並據此調整 SQL 語法、Index 配置、資料規格、甚至資料庫參數以改善查詢效能。

但解讀查詢計畫是一門複雜的學問，難以用規則去論述怎樣的查詢計畫肯定是最好的，就如[官方文檔](https://www.postgresql.org/docs/current/using-explain.html)所說，需要於從實務中累積經驗。(我也還在學習中😂)

>[!Note] 節選自官方文檔 14.1 節
> Plan-reading is an art that requires some experience to master.

這個章節旨在幫助讀者初步認識 Query Plan，習慣去檢視它，往後持續累積經驗💪

## PostgreSQL 語法執行程序

[圖片來源](https://www.postgresql.org/files/developer/tour.pdf)

當執行一段 SQL 查詢時，PostgreSQL 會經過以下四個階段。

### Step 1. Parser 解析器

PostgreSQL 會將 SQL 語句解析為內部資料結構。
這個階段會檢查語法是否正確，例如關鍵字拼寫、括號是否配對等，並將語句轉換為一棵語法樹 (Parse Tree)，再進一步轉為可供後續處理的查詢樹 (Query Tree)。

### Step 2. Rewrite 重寫階段

此階段將步驟 1 產出的步驟做重組優化。

例如，對 view 的查詢會被 rewrite 成實際查詢底層資料表的語句。

### Step 3. Planner/Optimizer 計畫器

針對查詢樹產生查詢計畫 (Query Plan)，計畫是由多個查詢節點 (Plan Node) 組成的計畫樹 (Plan Tree)。

此階段會一次產生多查詢策略，例如要不要使用 index、要做 Hash Join 還是 Nested Loop 等策略，並==根據統計資料 (pg_stats) 做成本估算==，選擇一個預期成本最低的計畫作為最終執行策略。

✅ 此階段的結果就是我們透過 `EXPLAIN` 指令所能看到的查詢計畫。

:::success

螢光筆字是重點，非常重要，**它明確表示了資料分佈會影響查詢計畫，所以同一段 SQL 用 A 客戶的資料測試起來跑超快，用 B 客戶的資料測試起來卻很慢 (即使 AB 客戶資料總筆數差不多) 是完全有可能且合理的。**

這也是開發者設計 SQL 時會遇到的難題，實務上很難預測這段 SQL 是否在所有不同資料量、資料分佈的排列組合下是否都能表現良好。

我認為能做的是盡量以實際資料或壓力夠大的測試資料去檢查 SQL 的查詢計畫。

:::

### Step 4. Executor 執行器

最後，Executor 會按照 Planner 產生的計畫實際執行查詢，從資料庫物件擷取資料並產生結果集。

✅ 若使用的是 `EXPLAIN ANALYZE` 指令，PostgreSQL 會執行查詢並同時回報實際的執行時間與回傳筆數，方便與預估成本進行比對。

## 如何知道 SQL 的 Query Plan

透過 [`EXPLAIN` 指令](https://www.postgresql.org/docs/current/sql-explain.html)可以取得 Query Plan，預設以文字格式輸出，如下圖。
(此指令只能對單獨一句完整的 SQL 使用。)

<center class="half">
<img src=https://hackmd.io/_uploads/S1aLnquOlg.png width="500"/>
</center>

有另一種圖形化的閱讀方式，可以直接用 pgAdmin 的 `EXPLAIN ANALYZE` 按鈕，此方式就不用寫 `EXPLAIN ANALYZE` 指令了。

<center class="half">
<img src=https://hackmd.io/_uploads/rkN_29__gl.png width="500"/>
</center>

切換到 Analysis Tab 同樣有文字版的查詢計畫。

<center class="half">
<img src=https://hackmd.io/_uploads/S1GYncuuex.png width="500"/>
</center>

## Query Plan 查詢計畫的解讀

Query Plan 是由多個查詢節點 (Plan Node) 組成的查詢樹 (Plan Tree)。

節點類型有很多種。
舉例來說，樹的最底端通常是 Scan 節點，這個節點是去資料實體檔案拿資料列回來；若有對資料做 `JOIN`、聚合、排序等操作，也會有相應的資料處理的節點。

>[!Tip] `EXPLAIN` 提供的節點資訊
>每個節點會顯示該節點的以下資訊:
>* `cost`: 包含兩個數值，格式為 `{Start-up Cost}...{Total Cost}`。不同節點的成本估算方式不同，相關參數也不同，但核心概念是預估對系統資源如 CPU、Memory、Disk 的用量。
> * `Start-up Cost`: 該節點開始前的預估成本。
> * `Total Cost`: 該節點完成的預估成本。
>* `rows`: 該節點產出資料的預估行數。會影響成本的計算。
>* `width`: 該節點輸出的 `rows` 的預估平均寬度 (單位: byte)。用於計算操作需要的記憶體大小，會影響查詢計劃對記憶體、磁碟使用的策略。
><center class="half">
> <img src=https://hackmd.io/_uploads/H1ukDUeFxx.png width="500"/>
></center>

>[!Tip] `EXPLAIN ANALYZE` 提供的節點資訊
>若執行時，有加上 `ANALYZE` 選項，則會在每個節點多出現一組實際執行結果的比對資料。
>* `actual time`: 包含兩個數值，格式為 `{Start-up Time}...{Total Time}`。
> * `Start-up Time`: 該節點開始前的實際執行時間(單位: ms)。
> * `Total Time`: 該節點完成的實際執行時間 (單位: ms)。
>* `rows`: 該節點產出資料的實際行數。
>* `loops`: 該節點的執行計劃實際被執行 N 次。
>
>並於查詢計劃的末兩行輸出
>* `Planning Time`: Planer 生成查詢計畫並對其做最佳化的時間，也就是前面提到的四階段中 Step 3. Planner/Optimizer 的花費時間。
>* `Execution Time`: 執行時間，是前面提到的四階段中 Step 4. Executor 的花費時間。
><center class="half">
> <img src=https://hackmd.io/_uploads/SkDwwUltel.png width="500"/>
></center>

每個上層節點的統計資訊，會包含下層節點的加總，所以數值上看起來上層節點的成本/時間 >= SUM(下層節點的成本/時間)。

閱讀上，我習慣把整個內容貼到自己慣用的文字編輯器後，==從縮排最深的地方開始往外閱讀==。

通常閱讀方式就是逐層確認，==找到造成的 `cost` / `actual time` 最大的節點，從最瓶頸的節點開始評估==。

### 常見 Plan Node

節點有非常多種類型 ➡️ [DeepWiki 分析 PostgreSQL 原始碼列出的所有節點](https://deepwiki.com/search/explain-query-plan-plan-node_7f8afd7b-0046-414a-9bc3-10eee3779b96)

有些類型我在實務上也沒看過，以下介紹常見節點，並於備註紀錄基於我目前的經驗、對該類型節點的補充說明。

:::spoiler 測試資料
為 Demo 各個 Plan Node，隨意建立幾張設備資料表當測試資料。
資料表分區的部分使用 pg_partman，請參考➡️[Partition 維護工具 pg_partman](https://hackmd.io/@moment89/pg_partman)

CREATE SCHEMA test;
CREATE SCHEMA partman;
CREATE EXTENSION pg_partman SCHEMA partman;

-- 設備類型
CREATE TABLE test.equipment_type (
    type_id bigserial PRIMARY KEY,
    type_name character varying NOT NULL,
    created_at timestamp with time zone DEFAULT now(),
    modified_at timestamp with time zone DEFAULT now()
);

INSERT INTO test.equipment_type (type_name) VALUES
('基礎設備'), ('中階設備'), ('高級設備');

-- 設備清單
CREATE TABLE test.equipment (
    equip_id bigserial PRIMARY KEY,
    equip_name character varying NOT NULL,
    equip_type int REFERENCES test.equipment_type(type_id) ON DELETE CASCADE ON UPDATE CASCADE,
    created_at timestamp with time zone DEFAULT now(),
    modified_at timestamp with time zone DEFAULT now()
);

INSERT INTO test.equipment (equip_name, equip_type) VALUES
('A01', 1), ('A02', 1), ('A03', 1), ('A04', 1), ('A05', 1),
('B01', 2), ('B02', 2), ('B03', 2), ('B04', 2), ('B05', 2),
('C01', 3), ('C02', 3), ('C03', 3), ('C04', 3), ('C05', 3);

-- 設備資料
CREATE TABLE test.equipment_data (
    id bigserial,
    equip_id int REFERENCES test.equipment(equip_id) ON DELETE CASCADE ON UPDATE CASCADE,
    data_topic character varying NOT NULL,
    data_value numeric NOT NULL,
    created_at timestamp with time zone DEFAULT now(),
    PRIMARY KEY (id, created_at)
) PARTITION BY RANGE (created_at);

SELECT partman.create_parent(
    p_parent_table := 'test.equipment_data' -- 父表
    , p_control := 'created_at' -- Partition Key
    , p_interval := '1 day' -- 每個分區的時間長度
    , p_type := 'range' -- range partition
    , p_premake := 7 -- 領先於當前時間的分區數
    , p_start_partition := (current_date-7)::varchar -- 起始分區的時間/數值
    , p_default_table := true -- 是否建立 Default Partition
    , p_automatic_maintenance := 'on' -- 是否啟動自動維護
    , p_control_not_null := true -- 是否 partition key 不能為 NULL
);

-- 假資料
INSERT INTO test.equipment_data (equip_id, data_topic, data_value, created_at)
SELECT
    random() * 14 + 1 AS equip_id,
    'power_status' AS data_topic,
    random()::integer AS data_value, -- 0 = 關機; 1 = 開機
    (current_date-7)::timestamp + (random() * 8 * 24 * 60 * 60 - 1) * INTERVAL '1 s' AS created_at
FROM generate_series(1, 1000000) AS i;

INSERT INTO test.equipment_data (equip_id, data_topic, data_value, created_at)
SELECT
    random() * 14 + 1 AS equip_id,
    'operation_status' AS data_topic,
    (random()*2)::integer AS data_value, -- 0 = 閒置; 1 = 運作; 2 = 異常
    (current_date-7)::timestamp + (random() * 8 * 24 * 60 * 60 - 1) * INTERVAL '1 s' AS created_at
FROM generate_series(1, 1000000) AS i;

INSERT INTO test.equipment_data (equip_id, data_topic, data_value, created_at)
SELECT
    random() * 14 + 1 AS equip_id,
    'quantity' AS data_topic,
    1 + random()*10::integer AS data_value,
    (current_date-7)::timestamp + (random() * 8 * 24 * 60 * 60 - 1) * INTERVAL '1 s' AS created_at
FROM generate_series(1, 1000000) AS i;

-- 測試 function scan
CREATE OR REPLACE FUNCTION test.get_current_power_status_by_equip(in_equip_id bigint)
RETURNS numeric
LANGUAGE plpgsql
AS $BODY$
DECLARE
    n_value numeric;
BEGIN
    SELECT
        data_value
    INTO n_value
    FROM test.equipment_data
    WHERE equip_id = in_equip_id
    AND data_topic = 'power_status'
    ORDER BY created_at DESC
    LIMIT 1;

    RETURN n_value;
END;
$BODY$;

:::

#### Scan Node - 讀取來源資料的節點

##### 💡Seq Scan

順序掃描，逐筆讀取資料掃瞄整張表。

>[!Note] 備註
>資料量大時效能不佳。

<center class="half">
<img src=https://hackmd.io/_uploads/HksyMDeYge.png width="500"/>
</center>

##### 💡Index Scan

使用 Index 定位資料所在的 Block 後至該 Table 的 Block 取得資料。
若 Index 命中多筆資料，會逐個對每筆資執行「拿出對應的 heap block → 取出資料」的流程。
若不同筆資料重複出現於同一個 Block，會需要重新載入。

>[!Note] 備註
>通常比 Sequential Scan 效能好。
>但因為要多次訪問兩個物件 (先訪問 index 物件，再訪問 Table 物件)，若 Planer 計算成本時認為多次 I/O 的成本太高時會選擇其他查詢計劃例如 Bitmap Index Scan，也可能都不使用 index 直接做 Sequential Scan。

<center class="half">
<img src=https://hackmd.io/_uploads/Sy1fzvlFxg.png width="500"/>
</center>

##### 💡Index Only Scan

直接從 index 結構中取得資料，無須訪問 Table 本身。

>[!Note] 備註
>建立索引時，若查詢情境經常是 SELECT {B 欄位} FROM table WHERE {A 欄位} = 條件，可以在對 A 欄位建立索引時，用 `INCLUDE` 選項包含 B 欄位，就能在此查詢情境實現 Index Only Scan。

<center class="half">
<img src=https://hackmd.io/_uploads/HJ0Riwetll.png width="500"/>
</center>

##### 💡Bitmap Index Scan

用所有查詢條件對應的 Index 紀錄條件中各 Index 符合的資料所在位置後，組成 Bitmap，找到資料的交集或聯集後，一次載入所有需要的 Block 後用 Bitmap Heap Scan 節點返回資料。

>[!Note] 備註
>以下兩種情況 Planer 會選擇使用此節點:
>1. 有多個條件需要組合多個索引，常見於 `WHERE` 條件裡有 `AND`、`OR` 等多個條件時。
>2. 當查詢條件命中太多筆資料，使用 Index Scan 逐筆回表查詢不划算時。(但若 Planer 估算 Bitmap Index Scan 一次要載入的 Block 過多、工作記憶體 `work_mem` 放不下需要溢出到磁碟時，可能會因 I/O 成本太高而退回用 Index Scan。)

<center class="half">
<img src=https://hackmd.io/_uploads/B1CF2veKgg.png width="500"/>
</center>

##### 💡Values Scan

掃描固定列出的值，例如 `SELECT * FROM (VALUES (1), (2)) AS t(test)`。

<center class="half">
<img src=https://hackmd.io/_uploads/rJa2hPlYeg.png width="500"/>
</center>

##### 💡Function Scan

掃描從函數回傳的資料表。

<center class="half">
<img src=https://hackmd.io/_uploads/Hk4HgdlYlg.png width="500"/>
</center>

#### Join Node - 資料合併節點

##### 💡Hash Join

對其中一個輸入 (通常會選擇資料量較小的那一方) 進行掃描組成 Hash Table，然後掃描另一個輸入逐個到 Hash Table 找出所需的資料。

>[!Note] 備註
當 `JOIN` 條件是等號運算 ( A = B )，且其中一方的資料量放得進 `work_mem`，Planer 會傾向使用此演算法。

<center class="half">
<img src=https://hackmd.io/_uploads/HJw1Jjetgg.png width="500"/>
</center>

##### 💡Nested Loop

對於外層查詢的每一筆資料逐筆執行內層的邏輯。
也就是說，若 Outer Table (外層資料) 有 M 筆、Inner Table (內層資料) 有 N 筆，會對內層資料掃描 M 次。
若每次掃描都使用 Seq Scan (最糟糕的情況)，則此查詢的時間複雜度是 O(M x N)。
此節點下的子節點，可以看到 `loops` 大於 1 的計畫，就是剛剛提到會對內層資料執行多次邏輯的表現。

>[!Note] 備註
>* 若 Outer Table 和 Inner Table 的串接條件有 Index，可以加速查詢。
>* 當兩表資料量差距懸殊，其中一張表特別小時，此演算法效益不錯。但若兩表資料量都非常大，則效益不佳。
>* 若 `JOIN` 條件是==非等號==的判斷式，Planer 會傾向使用此演算法。

<center class="half">
<img src=https://hackmd.io/_uploads/BkrrkoeYlx.png width="500"/>
</center>

##### 💡Merge Join

對兩個已依據 `JOIN` 條件欄位排序的資料進行連接。

>[!Note] 備註
>當 `JOIN` 條件是等號運算 ( A = B 且 `JOIN` 條件的欄位==有建立索引==，則 Planer 可以從 index 取得其排序，會傾向使用此演算法。

<center class="half">
<img src=https://hackmd.io/_uploads/B16MWogtgl.png width="500"/>
</center>

#### Operation Node - 資料操作節點

##### 💡Append

將多個子計劃的結果合併。

>[!Note] 備註
`UNION ALL` 或 Partition 表查詢時會使用此節點組合資料。

<center class="half">
<img src=https://hackmd.io/_uploads/SyROWixtll.png width="500"/>
</center>

##### 💡Aggregate

執行聚合函數計算，如 GROUP BY 計算 SUM、COUNT、MIN、MAX。
時間複雜度 O(n)。

>[!Note] 備註
>若 GROUP BY 欄位散佈度低 (Group 的數量少) 時效率不錯。
>但若 Group 有很多組會需要使用較多 Memory，當用量超過 `work_mem` 時會使用磁碟暫存檔案，導致效能大幅下降，Cost 顯著上升。

<center class="half">
<img src=https://hackmd.io/_uploads/HkbGfietee.png width="500"/>
</center>

##### 💡Limit

限制輸出的資料筆數，實現 `LIMIT` 和 `OFFSET` 功能。

>[!Note] 備註
>效能受限於 `OFFSET` 的大小，`OFFSET` 越大，效能越差。
>例如查詢 `LIMIT 10 OFFSET 100`，需運算到 110 筆才能返回結果。

<center class="half">
<img src=https://hackmd.io/_uploads/Hye6fjltxl.png width="500"/>
</center>

##### 💡Sort

對輸入的資料進行排序操作。
使用外部排序演算法，時間複雜度 O(n log n)。

>[!Note] 備註
>排序是成本挺高的操作。
>當資料量超過 `work_mem` 時會需要使用磁碟暫存檔案，導致效能大幅下降，Cost 顯著上升。

<center class="half">
<img src=https://hackmd.io/_uploads/HkhgXilYxg.png width="500"/>
</center>

##### 💡WindowAgg

執行 Window Functions 計算，如 `ROW_NUMBER() OVER()`、`RANK() OVER()`、`SUM() OVER()`、`LAG() OVER()`、`LEAD() OVER()` 等。

>[!Note] 備註
>每筆資料都需保留在記憶體中進行排序與計算，當用量超過 `work_mem` 時會使用磁碟暫存檔案，導致效能大幅下降，Cost 顯著上升。
>由於 Window Function 常搭配 Sort，排序成本高，在處理大型資料時可能會效能不佳。

<center class="half">
<img src=https://hackmd.io/_uploads/HyDv7oxKxl.png width="500"/>
</center>

## 個人優化 SQL 思路

### 改善 Scan Node

若資料量大時我會希望盡量讓查詢計劃趨向使用 index scan / index only scan / bitmap index scan。

若已有建立 index 而查詢計劃卻不使用，我會檢視以下項目
1. 查詢條件是否和 Index 相符?
2. 手動觸發更新統計資料，驗證是否資料庫的統計資料不精準造成?
3. 資料分佈、資料結構是否有改善空間?

### 改善 Join Node

若兩表資料量都大，我會希望盡量讓查詢計劃趨向使用 merge join / hash join。
若兩表資料量都大而查詢計劃選用 Nested Loop，我會檢視
1. 是否使用了非等號的 JOIN 條件?
2. 是否能以改寫 SQL 來避免?

### 改善 Operation Node

我會檢視是否某一資料操作 Cost 特別大，測試變更邏輯使用其他類型的運算或變更資料規格看是否能降低 Cost。

### 以變更資料結構改善查詢的案例

假設我有個查詢需要取得某一日期的設備運作狀態資料，需要回傳狀態的起迄時間。
查詢如下，我會需要先全表搜尋後用 Windows function 比對前後筆，才能篩選出與今日時間範圍重疊的資料。

<center class="half">
<img src=https://hackmd.io/_uploads/SkERA2lKgl.png width="500"/>
</center>

:::spoiler 查詢計劃完整內容

"Subquery Scan on cte_operation_status  (cost=32339.16..33810.15 rows=11536 width=27) (actual time=251.998..253.244 rows=4416 loops=1)"
"  Filter: ((cte_operation_status.end_time >= CURRENT_DATE) AND (cte_operation_status.start_time < (CURRENT_DATE + 1)))"
"  Rows Removed by Filter: 31661"
"  ->  WindowAgg  (cost=32339.16..33031.38 rows=34612 width=27) (actual time=243.797..251.288 rows=36077 loops=1)"
"        ->  Sort  (cost=32339.14..32425.67 rows=34612 width=19) (actual time=243.787..244.850 rows=36077 loops=1)"
"              Sort Key: equipment_data.created_at"
"              Sort Method: quicksort  Memory: 2875kB"
"              ->  Append  (cost=149.60..29729.57 rows=34612 width=19) (actual time=33.243..240.262 rows=36077 loops=1)"
"                    ->  Bitmap Heap Scan on equipment_data_p20250811 equipment_data_1  (cost=149.60..3691.76 rows=4396 width=19) (actual time=33.242..51.459 rows=4531 loops=1)"
"                          Recheck Cond: (equip_id = 1)"
"                          Filter: ((data_topic)::text = 'operation_status'::text)"
"                          Rows Removed by Filter: 8826"
"                          Heap Blocks: exact=3283"
"                          ->  Bitmap Index Scan on equipment_data_p20250811_equip_id_idx  (cost=0.00..148.50 rows=13344 width=0) (actual time=1.297..1.297 rows=13357 loops=1)"
"                                Index Cond: (equip_id = 1)"
"                    ->  Bitmap Heap Scan on equipment_data_p20250812 equipment_data_2  (cost=140.65..3687.00 rows=4225 width=19) (actual time=13.546..36.140 rows=4451 loops=1)"
"                          Recheck Cond: (equip_id = 1)"
"                          Filter: ((data_topic)::text = 'operation_status'::text)"
"                          Rows Removed by Filter: 9001"
"                          Heap Blocks: exact=3295"
"                          ->  Bitmap Index Scan on equipment_data_p20250812_equip_id_idx  (cost=0.00..139.60 rows=12690 width=0) (actual time=0.468..0.468 rows=13452 loops=1)"
"                                Index Cond: (equip_id = 1)"
"                    ->  Bitmap Heap Scan on equipment_data_p20250813 equipment_data_3  (cost=151.99..3703.86 rows=4546 width=19) (actual time=14.168..27.119 rows=4494 loops=1)"
"                          Recheck Cond: (equip_id = 1)"
"                          Filter: ((data_topic)::text = 'operation_status'::text)"
"                          Rows Removed by Filter: 8980"
"                          Heap Blocks: exact=3293"
"                          ->  Bitmap Index Scan on equipment_data_p20250813_equip_id_idx  (cost=0.00..150.86 rows=13658 width=0) (actual time=0.364..0.364 rows=13474 loops=1)"
"                                Index Cond: (equip_id = 1)"
"                    ->  Bitmap Heap Scan on equipment_data_p20250814 equipment_data_4  (cost=153.24..3703.64 rows=4445 width=19) (actual time=11.678..35.655 rows=4583 loops=1)"
"                          Recheck Cond: (equip_id = 1)"
"                          Filter: ((data_topic)::text = 'operation_status'::text)"
"                          Rows Removed by Filter: 8883"
"                          Heap Blocks: exact=3284"
"                          ->  Bitmap Index Scan on equipment_data_p20250814_equip_id_idx  (cost=0.00..152.12 rows=13827 width=0) (actual time=0.491..0.491 rows=13466 loops=1)"
"                                Index Cond: (equip_id = 1)"
"                    ->  Bitmap Heap Scan on equipment_data_p20250815 equipment_data_5  (cost=158.90..3712.48 rows=4733 width=19) (actual time=11.525..30.472 rows=4545 loops=1)"
"                          Recheck Cond: (equip_id = 1)"
"                          Filter: ((data_topic)::text = 'operation_status'::text)"
"                          Rows Removed by Filter: 9044"
"                          Heap Blocks: exact=3279"
"                          ->  Bitmap Index Scan on equipment_data_p20250815_equip_id_idx  (cost=0.00..157.72 rows=14039 width=0) (actual time=0.352..0.352 rows=13589 loops=1)"
"                                Index Cond: (equip_id = 1)"
"                    ->  Bitmap Heap Scan on equipment_data_p20250816 equipment_data_6  (cost=148.80..3686.73 rows=3648 width=19) (actual time=6.023..19.115 rows=4471 loops=1)"
"                          Recheck Cond: (equip_id = 1)"
"                          Filter: ((data_topic)::text = 'operation_status'::text)"
"                          Rows Removed by Filter: 8806"
"                          Heap Blocks: exact=3275"
"                          ->  Bitmap Index Scan on equipment_data_p20250816_equip_id_idx  (cost=0.00..147.89 rows=13262 width=0) (actual time=0.353..0.353 rows=13277 loops=1)"
"                                Index Cond: (equip_id = 1)"
"                    ->  Bitmap Heap Scan on equipment_data_p20250817 equipment_data_7  (cost=140.05..3680.20 rows=4222 width=19) (actual time=8.535..16.481 rows=4587 loops=1)"
"                          Recheck Cond: (equip_id = 1)"
"                          Filter: ((data_topic)::text = 'operation_status'::text)"
"                          Rows Removed by Filter: 8937"
"                          Heap Blocks: exact=3296"
"                          ->  Bitmap Index Scan on equipment_data_p20250817_equip_id_idx  (cost=0.00..139.00 rows=12610 width=0) (actual time=0.457..0.457 rows=13524 loops=1)"
"                                Index Cond: (equip_id = 1)"
"                    ->  Bitmap Heap Scan on equipment_data_p20250818 equipment_data_8  (cost=148.26..3689.73 rows=4389 width=19) (actual time=7.615..22.231 rows=4415 loops=1)"
"                          Recheck Cond: (equip_id = 1)"
"                          Filter: ((data_topic)::text = 'operation_status'::text)"
"                          Rows Removed by Filter: 8986"
"                          Heap Blocks: exact=3273"
"                          ->  Bitmap Index Scan on equipment_data_p20250818_equip_id_idx  (cost=0.00..147.16 rows=13165 width=0) (actual time=0.300..0.300 rows=13401 loops=1)"
"                                Index Cond: (equip_id = 1)"
"                    ->  Seq Scan on equipment_data_p20250819 equipment_data_9  (cost=0.00..0.00 rows=1 width=19) (actual time=0.007..0.007 rows=0 loops=1)"
"                          Filter: ((equip_id = 1) AND ((data_topic)::text = 'operation_status'::text))"
"                    ->  Seq Scan on equipment_data_p20250820 equipment_data_10  (cost=0.00..0.00 rows=1 width=19) (actual time=0.002..0.002 rows=0 loops=1)"
"                          Filter: ((equip_id = 1) AND ((data_topic)::text = 'operation_status'::text))"
"                    ->  Seq Scan on equipment_data_p20250821 equipment_data_11  (cost=0.00..0.00 rows=1 width=19) (actual time=0.002..0.002 rows=0 loops=1)"
"                          Filter: ((equip_id = 1) AND ((data_topic)::text = 'operation_status'::text))"
"                    ->  Seq Scan on equipment_data_p20250822 equipment_data_12  (cost=0.00..0.00 rows=1 width=19) (actual time=0.002..0.002 rows=0 loops=1)"
"                          Filter: ((equip_id = 1) AND ((data_topic)::text = 'operation_status'::text))"
"                    ->  Seq Scan on equipment_data_p20250823 equipment_data_13  (cost=0.00..0.00 rows=1 width=19) (actual time=0.002..0.002 rows=0 loops=1)"
"                          Filter: ((equip_id = 1) AND ((data_topic)::text = 'operation_status'::text))"
"                    ->  Seq Scan on equipment_data_p20250824 equipment_data_14  (cost=0.00..0.00 rows=1 width=19) (actual time=0.005..0.005 rows=0 loops=1)"
"                          Filter: ((equip_id = 1) AND ((data_topic)::text = 'operation_status'::text))"
"                    ->  Seq Scan on equipment_data_p20250825 equipment_data_15  (cost=0.00..0.00 rows=1 width=19) (actual time=0.002..0.002 rows=0 loops=1)"
"                          Filter: ((equip_id = 1) AND ((data_topic)::text = 'operation_status'::text))"
"                    ->  Seq Scan on equipment_data_default equipment_data_16  (cost=0.00..1.09 rows=1 width=19) (actual time=0.012..0.012 rows=0 loops=1)"
"                          Filter: ((equip_id = 1) AND ((data_topic)::text = 'operation_status'::text))"
"                          Rows Removed by Filter: 6"
"Planning Time: 5.347 ms"
"Execution Time: 253.735 ms"

:::

假設這個查詢情境很常發生，經查需要這樣組合前後筆資料，我會考慮==是否能犧牲一點資料 `INSERT` 的效能來換取查詢效率==，在 `equipment_data` 資料表上綁 Trigger，當 `equipment_data` 收到設備運作狀態的資料時，同時在新的資料表 `equipment_operation_status` 產生一筆整理好起訖時間的資料。

:::spoiler 資料規格異動 SQL

CREATE TABLE test.equipment_operation_status (
    equip_id int REFERENCES test.equipment(equip_id) ON DELETE CASCADE ON UPDATE CASCADE,
    operation_status integer,
    start_time timestamp with time zone not null,
    end_time timestamp with time zone,
    created_at timestamp with time zone DEFAULT now(),
    modified_at timestamp with time zone DEFAULT now()
);

CREATE UNIQUE INDEX idx_equipment_operation_status_equip_id_start_time_end_time
ON test.equipment_operation_status(equip_id, start_time, end_time) INCLUDE (operation_status);

CREATE INDEX idx_equipment_operation_status_equip_id_end_time_null
ON test.equipment_operation_status(equip_id, end_time) WHERE end_time IS NULL;

CREATE OR REPLACE FUNCTION test.tfn_after_insert_operation_status()
RETURNS trigger
LANGUAGE plpgsql
AS $BODY$
BEGIN
    IF NEW.data_topic != 'operation_status'
    THEN
        RETURN NEW;
    END IF;
    
    UPDATE test.equipment_operation_status
    SET end_time = NEW.created_at
    WHERE equip_id = NEW.equip_id
    AND end_time IS NULL;

    INSERT INTO test.equipment_operation_status (
        equip_id,
        operation_status,
        start_time
    ) VALUES (
        NEW.equip_id,
        NEW.data_value::integer,
        NEW.created_at
    );
    RETURN NEW;
END;
$BODY$;

CREATE TRIGGER t_after_insert_operation_status
AFTER INSERT ON test.equipment_data
FOR EACH ROW EXECUTE FUNCTION test.tfn_after_insert_operation_status();

:::

那麼我的查詢 SQL 就可以節省 Windows Function 的部分，直接查詢所需範圍的資料。

<center class="half">
<img src=https://hackmd.io/_uploads/SJ2UJTxFeg.png width="500"/>
</center>

## 結語

本篇主要是分享我對查詢計劃的理解，並介紹常見的 Plan Node 及特性，未來我對查詢計劃有新的見解會再持續更新到這篇！