---
# System prepended metadata

title: PostgreSQL Function Volatility Categories
tags: [PostgreSQL]

---

# PostgreSQL Function Volatility Categories
* [官方文檔](https://www.postgresql.org/docs/current/xfunc-volatility.html)

會注意到這件事是先前用 bigint 型別的 unixtimestamp 當 partition key 欄位，發現若在查詢條件做時間轉換，有查詢計劃分析不佳而全表查詢的情況。
可將此時間轉換部分包裝為函數 `to_unixtimestamp`，並將此函數設定為 `IMMUTABLE` 類別，此類別的函數在 PostgreSQL 查詢計畫中會被預先運算。

## Function Volatility Categories
### VOLATILE 易變的
PostgreSQL Function 預設類型。
此類 Function 會被 PostgreSQL 假設為「可能會修改資料庫」或「可能返回不確定的結果」，故 PostgreSQL 查詢器對此 Function 的優化會較保守，並且在每次查詢執行時都重新評估。
:::info
建立 temp table 也算是修改資料庫的行為，但 cte 不算。
:::

### STABLE 穩定的
此類 Function 不能修改資料庫，且在「同一個查詢」中相同參數會返回一樣的結果。
舉例來說，以下這個查詢，兩次調用 `stable_function`，若輸入的參數相同則必定返回相同結果。
```sql
SELECT 
    stable_function(column1),
    stable_function(column1)
FROM some_table;
```
而以下這個查詢，兩次調用 `stable_function` 是在不同查詢，輸入相同參數卻返回不同結果是允許的。
```sql
SELECT stable_function(column1) FROM table1;
SELECT stable_function(column1) FROM table1;
```

PostgreSQL 查詢器在同一個查詢中會重複使用函數結果，故須確保 Function 在同一個查詢中的結果一致性。
相較 `VOLATILE` 可提高查詢性能。

### IMMUTABLE 不可變的
此類 Function 不能修改資料庫，且相同的輸入參數「永遠」返回一樣的結果，也就是說 Function 結果完全依賴所輸入的參數。
適合純數學計算、不依賴資料庫內容的邏輯。
PostgreSQL 查詢器在進行規劃前就會先預先計算好此 Function 的結果，以產出更精確的查詢計劃。

## Demo

準備測試資料。
```sql
-- create partitioned table
create table public.bigint_partition_table (
    id uuid default gen_random_uuid(),
    content varchar not null,
    created_at bigint default round(extract(epoch from now())*1000),
    primary key (id, created_at)
) partition by range (created_at);

-- create partition by pg_partman
SELECT partman.create_parent(
    p_parent_table := 'public.bigint_partition_table' -- 父表
    , p_control := 'created_at' -- Partition Key
    , p_interval := '1 day' -- 每個分區的時間長度
    , p_type := 'range' -- 支援 range/list partition
    , p_epoch := 'milliseconds' -- 若 p_control 是 integer/bigint 欄位，透過此參數設定該數字所代表的意義 (seconds/milliseconds/microseconds/nanoseconds)
    , p_premake := 7 -- 領先於當前時間的分區數
    , p_start_partition := (current_date-30)::varchar -- 起始分區的時間/數值
    , p_default_table := true -- 是否建立 Default Partition
    , p_automatic_maintenance := 'on' -- 是否啟動自動維護
    -- , p_constraint_cols := NULL -- 約束條件
    -- , p_template_table := NULL -- 模板表
    , p_jobmon := false -- 是否透過 pg_jobmon 監控 pg_partman 的工作 (我沒安裝故設定 false)
    -- , p_date_trunc_interval := NULL -- Partition 時間截斷方式
    , p_control_not_null := true -- 是否 partition key 不能為 NULL
    -- , p_time_encoder := NULL -- 若 Partition Key 是非標準的時間格式或非時間類型，則需在此參數提供將 Partition Key 轉換為時間格式的函數
    -- , p_time_decoder := NULL -- 若 Partition Key 是非標準的時間格式或非時間類型，則需在此參數提供將時間格式轉換為 Partition Key 的函數
);

INSERT INTO public.bigint_partition_table(content, created_at)
SELECT
    md5(random()::text) AS content,
    1746633600000::bigint + (2678399999 * random())::bigint AS created_at
FROM generate_series(1, 1000000);
```

直接在 `WHERE` 條件做時間轉換。
```sql
EXPLAIN ANALYZE
SELECT
    content
FROM public.bigint_partition_table
WHERE created_at
    BETWEEN round(extract(epoch from current_date - 10)*1000)
    AND round(extract(epoch from now())*1000);
```

查詢計劃如下，可以看到是把所有分區都翻出來搜尋了，Partition Key 等於沒用。
:::spoiler
```text
"Gather  (cost=1000.00..38162.01 rows=5040 width=33) (actual time=43.971..146.031 rows=331513 loops=1)"
"  Workers Planned: 5"
"  Workers Launched: 5"
"  ->  Parallel Append  (cost=0.00..36658.01 rows=1003 width=33) (actual time=35.442..103.826 rows=55252 loops=6)"
"        ->  Parallel Seq Scan on bigint_partition_table_p20250607 bigint_partition_table_31  (cost=0.00..1188.80 rows=96 width=33) (actual time=0.716..24.754 rows=19660 loops=1)"
"              Filter: (((created_at)::numeric <= round((EXTRACT(epoch FROM now()) * '1000'::numeric), 0)) AND ((created_at)::numeric >= round((EXTRACT(epoch FROM (CURRENT_DATE - 10)) * '1000'::numeric), 0)))"
"              Rows Removed by Filter: 13012"
"        ->  Parallel Seq Scan on bigint_partition_table_p20250531 bigint_partition_table_24  (cost=0.00..1186.28 rows=96 width=33) (actual time=0.470..26.978 rows=32611 loops=1)"
"              Filter: (((created_at)::numeric <= round((EXTRACT(epoch FROM now()) * '1000'::numeric), 0)) AND ((created_at)::numeric >= round((EXTRACT(epoch FROM (CURRENT_DATE - 10)) * '1000'::numeric), 0)))"
"        ->  Parallel Seq Scan on bigint_partition_table_p20250520 bigint_partition_table_13  (cost=0.00..1183.00 rows=96 width=33) (actual time=50.027..50.027 rows=0 loops=1)"
"              Filter: (((created_at)::numeric <= round((EXTRACT(epoch FROM now()) * '1000'::numeric), 0)) AND ((created_at)::numeric >= round((EXTRACT(epoch FROM (CURRENT_DATE - 10)) * '1000'::numeric), 0)))"
"              Rows Removed by Filter: 32520"
"        ->  Parallel Seq Scan on bigint_partition_table_p20250518 bigint_partition_table_11  (cost=0.00..1182.45 rows=95 width=33) (actual time=40.457..40.457 rows=0 loops=1)"
"              Filter: (((created_at)::numeric <= round((EXTRACT(epoch FROM now()) * '1000'::numeric), 0)) AND ((created_at)::numeric >= round((EXTRACT(epoch FROM (CURRENT_DATE - 10)) * '1000'::numeric), 0)))"
"              Rows Removed by Filter: 32498"
"        ->  Parallel Seq Scan on bigint_partition_table_p20250530 bigint_partition_table_23  (cost=0.00..1180.62 rows=95 width=33) (actual time=0.074..23.142 rows=32465 loops=1)"
"              Filter: (((created_at)::numeric <= round((EXTRACT(epoch FROM now()) * '1000'::numeric), 0)) AND ((created_at)::numeric >= round((EXTRACT(epoch FROM (CURRENT_DATE - 10)) * '1000'::numeric), 0)))"
"        ->  Parallel Seq Scan on bigint_partition_table_p20250516 bigint_partition_table_9  (cost=0.00..1180.40 rows=95 width=33) (actual time=16.851..16.851 rows=0 loops=1)"
"              Filter: (((created_at)::numeric <= round((EXTRACT(epoch FROM now()) * '1000'::numeric), 0)) AND ((created_at)::numeric >= round((EXTRACT(epoch FROM (CURRENT_DATE - 10)) * '1000'::numeric), 0)))"
"              Rows Removed by Filter: 32456"
"        ->  Parallel Seq Scan on bigint_partition_table_p20250601 bigint_partition_table_25  (cost=0.00..1179.35 rows=95 width=33) (actual time=0.017..18.888 rows=32414 loops=1)"
"              Filter: (((created_at)::numeric <= round((EXTRACT(epoch FROM now()) * '1000'::numeric), 0)) AND ((created_at)::numeric >= round((EXTRACT(epoch FROM (CURRENT_DATE - 10)) * '1000'::numeric), 0)))"
"        ->  Parallel Seq Scan on bigint_partition_table_p20250528 bigint_partition_table_21  (cost=0.00..1178.62 rows=95 width=33) (actual time=0.028..19.472 rows=21549 loops=1)"
"              Filter: (((created_at)::numeric <= round((EXTRACT(epoch FROM now()) * '1000'::numeric), 0)) AND ((created_at)::numeric >= round((EXTRACT(epoch FROM (CURRENT_DATE - 10)) * '1000'::numeric), 0)))"
"              Rows Removed by Filter: 10836"
"        ->  Parallel Seq Scan on bigint_partition_table_p20250602 bigint_partition_table_26  (cost=0.00..1177.43 rows=95 width=33) (actual time=0.020..21.165 rows=32377 loops=1)"
"              Filter: (((created_at)::numeric <= round((EXTRACT(epoch FROM now()) * '1000'::numeric), 0)) AND ((created_at)::numeric >= round((EXTRACT(epoch FROM (CURRENT_DATE - 10)) * '1000'::numeric), 0)))"
"        ->  Parallel Seq Scan on bigint_partition_table_p20250514 bigint_partition_table_7  (cost=0.00..1177.10 rows=95 width=33) (actual time=14.787..14.787 rows=0 loops=1)"
"              Filter: (((created_at)::numeric <= round((EXTRACT(epoch FROM now()) * '1000'::numeric), 0)) AND ((created_at)::numeric >= round((EXTRACT(epoch FROM (CURRENT_DATE - 10)) * '1000'::numeric), 0)))"
"              Rows Removed by Filter: 32364"
"        ->  Parallel Seq Scan on bigint_partition_table_p20250524 bigint_partition_table_17  (cost=0.00..1176.47 rows=95 width=33) (actual time=13.843..13.843 rows=0 loops=1)"
"              Filter: (((created_at)::numeric <= round((EXTRACT(epoch FROM now()) * '1000'::numeric), 0)) AND ((created_at)::numeric >= round((EXTRACT(epoch FROM (CURRENT_DATE - 10)) * '1000'::numeric), 0)))"
"              Rows Removed by Filter: 32339"
"        ->  Parallel Seq Scan on bigint_partition_table_p20250511 bigint_partition_table_4  (cost=0.00..1175.93 rows=95 width=33) (actual time=16.388..16.388 rows=0 loops=1)"
"              Filter: (((created_at)::numeric <= round((EXTRACT(epoch FROM now()) * '1000'::numeric), 0)) AND ((created_at)::numeric >= round((EXTRACT(epoch FROM (CURRENT_DATE - 10)) * '1000'::numeric), 0)))"
"              Rows Removed by Filter: 32317"
"        ->  Parallel Seq Scan on bigint_partition_table_p20250517 bigint_partition_table_10  (cost=0.00..1174.28 rows=95 width=33) (actual time=17.523..17.523 rows=0 loops=1)"
"              Filter: (((created_at)::numeric <= round((EXTRACT(epoch FROM now()) * '1000'::numeric), 0)) AND ((created_at)::numeric >= round((EXTRACT(epoch FROM (CURRENT_DATE - 10)) * '1000'::numeric), 0)))"
"              Rows Removed by Filter: 32291"
"        ->  Parallel Seq Scan on bigint_partition_table_p20250509 bigint_partition_table_2  (cost=0.00..1173.73 rows=95 width=33) (actual time=20.752..20.752 rows=0 loops=1)"
"              Filter: (((created_at)::numeric <= round((EXTRACT(epoch FROM now()) * '1000'::numeric), 0)) AND ((created_at)::numeric >= round((EXTRACT(epoch FROM (CURRENT_DATE - 10)) * '1000'::numeric), 0)))"
"              Rows Removed by Filter: 32269"
"        ->  Parallel Seq Scan on bigint_partition_table_p20250519 bigint_partition_table_12  (cost=0.00..1173.28 rows=95 width=33) (actual time=22.864..22.864 rows=0 loops=1)"
"              Filter: (((created_at)::numeric <= round((EXTRACT(epoch FROM now()) * '1000'::numeric), 0)) AND ((created_at)::numeric >= round((EXTRACT(epoch FROM (CURRENT_DATE - 10)) * '1000'::numeric), 0)))"
"              Rows Removed by Filter: 32251"
"        ->  Parallel Seq Scan on bigint_partition_table_p20250515 bigint_partition_table_8  (cost=0.00..1173.00 rows=95 width=33) (actual time=15.592..15.592 rows=0 loops=1)"
"              Filter: (((created_at)::numeric <= round((EXTRACT(epoch FROM now()) * '1000'::numeric), 0)) AND ((created_at)::numeric >= round((EXTRACT(epoch FROM (CURRENT_DATE - 10)) * '1000'::numeric), 0)))"
"              Rows Removed by Filter: 32240"
"        ->  Parallel Seq Scan on bigint_partition_table_p20250527 bigint_partition_table_20  (cost=0.00..1172.70 rows=95 width=33) (actual time=13.461..13.462 rows=0 loops=1)"
"              Filter: (((created_at)::numeric <= round((EXTRACT(epoch FROM now()) * '1000'::numeric), 0)) AND ((created_at)::numeric >= round((EXTRACT(epoch FROM (CURRENT_DATE - 10)) * '1000'::numeric), 0)))"
"              Rows Removed by Filter: 32228"
"        ->  Parallel Seq Scan on bigint_partition_table_p20250512 bigint_partition_table_5  (cost=0.00..1172.60 rows=95 width=33) (actual time=13.713..13.713 rows=0 loops=1)"
"              Filter: (((created_at)::numeric <= round((EXTRACT(epoch FROM now()) * '1000'::numeric), 0)) AND ((created_at)::numeric >= round((EXTRACT(epoch FROM (CURRENT_DATE - 10)) * '1000'::numeric), 0)))"
"              Rows Removed by Filter: 32224"
"        ->  Parallel Seq Scan on bigint_partition_table_p20250522 bigint_partition_table_15  (cost=0.00..1170.60 rows=95 width=33) (actual time=12.981..12.981 rows=0 loops=1)"
"              Filter: (((created_at)::numeric <= round((EXTRACT(epoch FROM now()) * '1000'::numeric), 0)) AND ((created_at)::numeric >= round((EXTRACT(epoch FROM (CURRENT_DATE - 10)) * '1000'::numeric), 0)))"
"              Rows Removed by Filter: 32184"
"        ->  Parallel Seq Scan on bigint_partition_table_p20250603 bigint_partition_table_27  (cost=0.00..1170.00 rows=95 width=33) (actual time=0.007..8.273 rows=16080 loops=2)"
"              Filter: (((created_at)::numeric <= round((EXTRACT(epoch FROM now()) * '1000'::numeric), 0)) AND ((created_at)::numeric >= round((EXTRACT(epoch FROM (CURRENT_DATE - 10)) * '1000'::numeric), 0)))"
"        ->  Parallel Seq Scan on bigint_partition_table_p20250526 bigint_partition_table_19  (cost=0.00..1169.80 rows=95 width=33) (actual time=15.864..15.864 rows=0 loops=1)"
"              Filter: (((created_at)::numeric <= round((EXTRACT(epoch FROM now()) * '1000'::numeric), 0)) AND ((created_at)::numeric >= round((EXTRACT(epoch FROM (CURRENT_DATE - 10)) * '1000'::numeric), 0)))"
"              Rows Removed by Filter: 32152"
"        ->  Parallel Seq Scan on bigint_partition_table_p20250606 bigint_partition_table_30  (cost=0.00..1169.65 rows=95 width=33) (actual time=0.009..5.827 rows=10715 loops=3)"
"              Filter: (((created_at)::numeric <= round((EXTRACT(epoch FROM now()) * '1000'::numeric), 0)) AND ((created_at)::numeric >= round((EXTRACT(epoch FROM (CURRENT_DATE - 10)) * '1000'::numeric), 0)))"
"        ->  Parallel Seq Scan on bigint_partition_table_p20250510 bigint_partition_table_3  (cost=0.00..1169.10 rows=95 width=33) (actual time=10.662..10.662 rows=0 loops=1)"
"              Filter: (((created_at)::numeric <= round((EXTRACT(epoch FROM now()) * '1000'::numeric), 0)) AND ((created_at)::numeric >= round((EXTRACT(epoch FROM (CURRENT_DATE - 10)) * '1000'::numeric), 0)))"
"              Rows Removed by Filter: 32124"
"        ->  Parallel Seq Scan on bigint_partition_table_p20250604 bigint_partition_table_28  (cost=0.00..1169.03 rows=95 width=33) (actual time=0.013..2.614 rows=5354 loops=6)"
"              Filter: (((created_at)::numeric <= round((EXTRACT(epoch FROM now()) * '1000'::numeric), 0)) AND ((created_at)::numeric >= round((EXTRACT(epoch FROM (CURRENT_DATE - 10)) * '1000'::numeric), 0)))"
"        ->  Parallel Seq Scan on bigint_partition_table_p20250521 bigint_partition_table_14  (cost=0.00..1167.80 rows=95 width=33) (actual time=5.453..5.454 rows=0 loops=2)"
"              Filter: (((created_at)::numeric <= round((EXTRACT(epoch FROM now()) * '1000'::numeric), 0)) AND ((created_at)::numeric >= round((EXTRACT(epoch FROM (CURRENT_DATE - 10)) * '1000'::numeric), 0)))"
"              Rows Removed by Filter: 16056"
"        ->  Parallel Seq Scan on bigint_partition_table_p20250508 bigint_partition_table_1  (cost=0.00..1166.97 rows=94 width=33) (actual time=4.938..4.938 rows=0 loops=2)"
"              Filter: (((created_at)::numeric <= round((EXTRACT(epoch FROM now()) * '1000'::numeric), 0)) AND ((created_at)::numeric >= round((EXTRACT(epoch FROM (CURRENT_DATE - 10)) * '1000'::numeric), 0)))"
"              Rows Removed by Filter: 16040"
"        ->  Parallel Seq Scan on bigint_partition_table_p20250525 bigint_partition_table_18  (cost=0.00..1165.85 rows=94 width=33) (actual time=10.518..10.518 rows=0 loops=1)"
"              Filter: (((created_at)::numeric <= round((EXTRACT(epoch FROM now()) * '1000'::numeric), 0)) AND ((created_at)::numeric >= round((EXTRACT(epoch FROM (CURRENT_DATE - 10)) * '1000'::numeric), 0)))"
"              Rows Removed by Filter: 32034"
"        ->  Parallel Seq Scan on bigint_partition_table_p20250513 bigint_partition_table_6  (cost=0.00..1164.75 rows=94 width=33) (actual time=13.020..13.020 rows=0 loops=1)"
"              Filter: (((created_at)::numeric <= round((EXTRACT(epoch FROM now()) * '1000'::numeric), 0)) AND ((created_at)::numeric >= round((EXTRACT(epoch FROM (CURRENT_DATE - 10)) * '1000'::numeric), 0)))"
"              Rows Removed by Filter: 32030"
"        ->  Parallel Seq Scan on bigint_partition_table_p20250605 bigint_partition_table_29  (cost=0.00..1164.18 rows=94 width=33) (actual time=0.011..17.602 rows=32007 loops=1)"
"              Filter: (((created_at)::numeric <= round((EXTRACT(epoch FROM now()) * '1000'::numeric), 0)) AND ((created_at)::numeric >= round((EXTRACT(epoch FROM (CURRENT_DATE - 10)) * '1000'::numeric), 0)))"
"        ->  Parallel Seq Scan on bigint_partition_table_p20250529 bigint_partition_table_22  (cost=0.00..1164.08 rows=94 width=33) (actual time=0.009..17.338 rows=32003 loops=1)"
"              Filter: (((created_at)::numeric <= round((EXTRACT(epoch FROM now()) * '1000'::numeric), 0)) AND ((created_at)::numeric >= round((EXTRACT(epoch FROM (CURRENT_DATE - 10)) * '1000'::numeric), 0)))"
"        ->  Parallel Seq Scan on bigint_partition_table_p20250523 bigint_partition_table_16  (cost=0.00..1161.18 rows=94 width=33) (actual time=39.968..39.968 rows=0 loops=1)"
"              Filter: (((created_at)::numeric <= round((EXTRACT(epoch FROM now()) * '1000'::numeric), 0)) AND ((created_at)::numeric >= round((EXTRACT(epoch FROM (CURRENT_DATE - 10)) * '1000'::numeric), 0)))"
"              Rows Removed by Filter: 31927"
"        ->  Parallel Seq Scan on bigint_partition_table_p20250608 bigint_partition_table_32  (cost=0.00..34.25 rows=3 width=32) (actual time=0.001..0.001 rows=0 loops=1)"
"              Filter: (((created_at)::numeric <= round((EXTRACT(epoch FROM now()) * '1000'::numeric), 0)) AND ((created_at)::numeric >= round((EXTRACT(epoch FROM (CURRENT_DATE - 10)) * '1000'::numeric), 0)))"
"        ->  Parallel Seq Scan on bigint_partition_table_p20250609 bigint_partition_table_33  (cost=0.00..34.25 rows=3 width=32) (actual time=0.000..0.001 rows=0 loops=1)"
"              Filter: (((created_at)::numeric <= round((EXTRACT(epoch FROM now()) * '1000'::numeric), 0)) AND ((created_at)::numeric >= round((EXTRACT(epoch FROM (CURRENT_DATE - 10)) * '1000'::numeric), 0)))"
"        ->  Parallel Seq Scan on bigint_partition_table_p20250610 bigint_partition_table_34  (cost=0.00..34.25 rows=3 width=32) (actual time=0.001..0.001 rows=0 loops=1)"
"              Filter: (((created_at)::numeric <= round((EXTRACT(epoch FROM now()) * '1000'::numeric), 0)) AND ((created_at)::numeric >= round((EXTRACT(epoch FROM (CURRENT_DATE - 10)) * '1000'::numeric), 0)))"
"        ->  Parallel Seq Scan on bigint_partition_table_p20250611 bigint_partition_table_35  (cost=0.00..34.25 rows=3 width=32) (actual time=0.001..0.001 rows=0 loops=1)"
"              Filter: (((created_at)::numeric <= round((EXTRACT(epoch FROM now()) * '1000'::numeric), 0)) AND ((created_at)::numeric >= round((EXTRACT(epoch FROM (CURRENT_DATE - 10)) * '1000'::numeric), 0)))"
"        ->  Parallel Seq Scan on bigint_partition_table_p20250612 bigint_partition_table_36  (cost=0.00..34.25 rows=3 width=32) (actual time=0.000..0.001 rows=0 loops=1)"
"              Filter: (((created_at)::numeric <= round((EXTRACT(epoch FROM now()) * '1000'::numeric), 0)) AND ((created_at)::numeric >= round((EXTRACT(epoch FROM (CURRENT_DATE - 10)) * '1000'::numeric), 0)))"
"        ->  Parallel Seq Scan on bigint_partition_table_p20250613 bigint_partition_table_37  (cost=0.00..34.25 rows=3 width=32) (actual time=0.001..0.001 rows=0 loops=1)"
"              Filter: (((created_at)::numeric <= round((EXTRACT(epoch FROM now()) * '1000'::numeric), 0)) AND ((created_at)::numeric >= round((EXTRACT(epoch FROM (CURRENT_DATE - 10)) * '1000'::numeric), 0)))"
"        ->  Parallel Seq Scan on bigint_partition_table_p20250614 bigint_partition_table_38  (cost=0.00..34.25 rows=3 width=32) (actual time=0.001..0.001 rows=0 loops=1)"
"              Filter: (((created_at)::numeric <= round((EXTRACT(epoch FROM now()) * '1000'::numeric), 0)) AND ((created_at)::numeric >= round((EXTRACT(epoch FROM (CURRENT_DATE - 10)) * '1000'::numeric), 0)))"
"        ->  Parallel Seq Scan on bigint_partition_table_default bigint_partition_table_39  (cost=0.00..34.25 rows=3 width=32) (actual time=0.004..0.004 rows=0 loops=1)"
"              Filter: (((created_at)::numeric <= round((EXTRACT(epoch FROM now()) * '1000'::numeric), 0)) AND ((created_at)::numeric >= round((EXTRACT(epoch FROM (CURRENT_DATE - 10)) * '1000'::numeric), 0)))"
"Planning Time: 5.165 ms"
"Execution Time: 158.593 ms"
```
:::

測試把轉換時間的部分包成 `IMMUTABLE` 函數。
```sql
CREATE OR REPLACE FUNCTION public.to_unixtimestamp(timestamp with time zone)
RETURNS bigint
LANGUAGE 'plpgsql'
IMMUTABLE
AS $BODY$
BEGIN
    RETURN round(extract(epoch from $1) * 1000);
END;
$BODY$;
```

```sql
EXPLAIN ANALYZE
SELECT
    content
FROM public.bigint_partition_table
WHERE created_at
    BETWEEN to_unixtimestamp(current_date-10)
    AND to_unixtimestamp(now());
```

查詢計劃如下，變成精準查詢指定分區了。
:::spoiler
```text
"Append  (cost=0.80..27027.21 rows=341694 width=33) (actual time=0.038..128.206 rows=341724 loops=1)"
"  Subplans Removed: 28"
"  ->  Index Scan using bigint_partition_table_p20250528_pkey on bigint_partition_table_p20250528 bigint_partition_table_1  (cost=0.80..1290.84 rows=32379 width=33) (actual time=0.037..17.515 rows=32385 loops=1)"
"        Index Cond: ((created_at >= to_unixtimestamp(((CURRENT_DATE - 10))::timestamp with time zone)) AND (created_at <= to_unixtimestamp(now())))"
"  ->  Index Scan using bigint_partition_table_p20250529_pkey on bigint_partition_table_p20250529 bigint_partition_table_2  (cost=0.80..1268.90 rows=31997 width=33) (actual time=0.071..13.010 rows=32003 loops=1)"
"        Index Cond: ((created_at >= to_unixtimestamp(((CURRENT_DATE - 10))::timestamp with time zone)) AND (created_at <= to_unixtimestamp(now())))"
"  ->  Index Scan using bigint_partition_table_p20250530_pkey on bigint_partition_table_p20250530 bigint_partition_table_3  (cost=0.80..1295.73 rows=32459 width=33) (actual time=0.030..9.589 rows=32465 loops=1)"
"        Index Cond: ((created_at >= to_unixtimestamp(((CURRENT_DATE - 10))::timestamp with time zone)) AND (created_at <= to_unixtimestamp(now())))"
"  ->  Index Scan using bigint_partition_table_p20250531_pkey on bigint_partition_table_p20250531 bigint_partition_table_4  (cost=0.80..1312.95 rows=32604 width=33) (actual time=0.031..8.208 rows=32611 loops=1)"
"        Index Cond: ((created_at >= to_unixtimestamp(((CURRENT_DATE - 10))::timestamp with time zone)) AND (created_at <= to_unixtimestamp(now())))"
"  ->  Index Scan using bigint_partition_table_p20250601_pkey on bigint_partition_table_p20250601 bigint_partition_table_5  (cost=0.80..1294.72 rows=32408 width=33) (actual time=0.026..23.642 rows=32414 loops=1)"
"        Index Cond: ((created_at >= to_unixtimestamp(((CURRENT_DATE - 10))::timestamp with time zone)) AND (created_at <= to_unixtimestamp(now())))"
"  ->  Index Scan using bigint_partition_table_p20250602_pkey on bigint_partition_table_p20250602 bigint_partition_table_6  (cost=0.80..1286.28 rows=32371 width=33) (actual time=0.051..8.158 rows=32377 loops=1)"
"        Index Cond: ((created_at >= to_unixtimestamp(((CURRENT_DATE - 10))::timestamp with time zone)) AND (created_at <= to_unixtimestamp(now())))"
"  ->  Index Scan using bigint_partition_table_p20250603_pkey on bigint_partition_table_p20250603 bigint_partition_table_7  (cost=0.80..1279.74 rows=32154 width=33) (actual time=0.037..6.889 rows=32160 loops=1)"
"        Index Cond: ((created_at >= to_unixtimestamp(((CURRENT_DATE - 10))::timestamp with time zone)) AND (created_at <= to_unixtimestamp(now())))"
"  ->  Index Scan using bigint_partition_table_p20250604_pkey on bigint_partition_table_p20250604 bigint_partition_table_8  (cost=0.80..1266.86 rows=32115 width=33) (actual time=0.026..6.955 rows=32121 loops=1)"
"        Index Cond: ((created_at >= to_unixtimestamp(((CURRENT_DATE - 10))::timestamp with time zone)) AND (created_at <= to_unixtimestamp(now())))"
"  ->  Index Scan using bigint_partition_table_p20250605_pkey on bigint_partition_table_p20250605 bigint_partition_table_9  (cost=0.80..1277.78 rows=32001 width=33) (actual time=0.030..6.942 rows=32007 loops=1)"
"        Index Cond: ((created_at >= to_unixtimestamp(((CURRENT_DATE - 10))::timestamp with time zone)) AND (created_at <= to_unixtimestamp(now())))"
"  ->  Index Scan using bigint_partition_table_p20250606_pkey on bigint_partition_table_p20250606 bigint_partition_table_10  (cost=0.80..1280.56 rows=32140 width=33) (actual time=0.027..7.296 rows=32146 loops=1)"
"        Index Cond: ((created_at >= to_unixtimestamp(((CURRENT_DATE - 10))::timestamp with time zone)) AND (created_at <= to_unixtimestamp(now())))"
"  ->  Index Scan using bigint_partition_table_p20250607_pkey on bigint_partition_table_p20250607 bigint_partition_table_11  (cost=0.80..1163.28 rows=19006 width=33) (actual time=0.031..5.275 rows=19035 loops=1)"
"        Index Cond: ((created_at >= to_unixtimestamp(((CURRENT_DATE - 10))::timestamp with time zone)) AND (created_at <= to_unixtimestamp(now())))"
"Planning Time: 1.881 ms"
"Execution Time: 135.339 ms"
```
:::