-- 創(chuàng)建范圍分區(qū)表
CREATE TABLE sales (
    id serial,
    sale_date date,
    amount numeric
) PARTITION BY RANGE (sale_date);

-- 創(chuàng)建年度分區(qū)
CREATE TABLE sales_2020 PARTITION OF sales
    FOR VALUES FROM ('2020-01-01') TO ('2021-01-01');
CREATE TABLE sales_2021 PARTITION OF sales
    FOR VALUES FROM ('2021-01-01') TO ('2022-01-01');

-- 高效查詢(觸發(fā)裁剪)
EXPLAIN ANALYZE
SELECT * FROM sales![在這里插入圖片描述](https://i-blog.csdnimg.cn/direct/334b48d2fab14bdf8fbe51cb52fee92a.png)

WHERE sale_date BETWEEN '2021-06-01' AND '2021-12-31';

-- 創(chuàng)建列表分區(qū)表
CREATE TABLE orders (
    id serial,
    customer_type text,
    amount numeric
) PARTITION BY LIST (customer_type);

-- 創(chuàng)建分類分區(qū)
CREATE TABLE orders_retail PARTITION OF orders
    FOR VALUES IN ('retail');
CREATE TABLE orders_wholesale PARTITION OF orders
    FOR VALUES IN ('wholesale');

-- 高效查詢(觸發(fā)裁剪)
EXPLAIN ANALYZE
SELECT * FROM orders
WHERE customer_type = 'retail';

-- 直接使用分區(qū)鍵
WHERE partition_key = value
WHERE partition_key BETWEEN x AND y
WHERE partition_key IN (value1, value2)

-- 函數包裝分區(qū)鍵(無法裁剪)
WHERE UPPER(partition_key) = 'VALUE'
WHERE DATE_TRUNC('month', partition_key) = '2021-01-01'

-- 復雜OR條件
WHERE (partition_key = 1 OR other_column = 'value')

->  Seq Scan on sales_2021  (cost=0.00..123.45 rows=100 width=40)
   Filter: (sale_date >= '2021-06-01'::date AND sale_date <= '2021-12-31'::date)

-- 查看分區(qū)表統(tǒng)計
SELECT * FROM pg_stat_user_tables WHERE relname = 'sales';

-- 查看各分區(qū)統(tǒng)計
SELECT * FROM pg_stat_user_tables WHERE relname LIKE 'sales_%';

CREATE TABLE logs (
    id serial,
    log_date date,
    server_id int,
    message text
) PARTITION BY RANGE (log_date, server_id);

-- 高效查詢
EXPLAIN ANALYZE
SELECT * FROM logs
WHERE log_date = '2023-01-01' AND server_id = 3;