本章将涵盖以下主题：
- 分区基本概念
- 使用表继承进行分区
- 声明式分区
基本概念
文档参考这里。
💡 分区是指将逻辑上一个大表拆分成更小的物理块。
分区可以带来以下几个好处：
- 在某些情况下，查询性能可以显著提升，尤其是当表中大多数访问频繁的行位于单个分区或少数几个分区中时。分区有效地替代了索引的上层树状结构，使索引中访问频繁的部分更有可能被加载到内存中。
- 当查询或更新访问单个分区的很大一部分时，可以通过对该分区进行顺序扫描（而不是使用索引）来提高性能，因为索引需要在整个表中进行随机访问读取。
- 如果在分区设计中考虑了使用模式，可以通过添加或删除分区来完成批量加载和删除。使用 DROP TABLE 或执行 ALTER TABLE DETACH PARTITION 删除单个分区比批量操作快得多。这些命令还可以完全避免批量 DELETE 操作带来的 VACUUM 开销。
- 不常用的数据可以迁移到更便宜、更慢的存储介质上。
这些好处通常只有在表非常大的情况下才会体现出来。表从分区中获益的具体程度取决于应用程序，但经验法则是表的大小应该超过数据库服务器的物理内存。
💡 作者介绍了一个根据硬件配置生成配置文件的网站PGTune
总的来说，表越小，缓存在shared buffer的几率就越大，性能就越好。默认的shared buffer大小是128M：
postgres=# show shared_buffers;
shared_buffers
----------------
128MB
(1 row)
可以用两种方式定义表的分区：
- 内置的声明式分区
- 使用继承进行分区
💡 自版本 10 以来，声明式分区已成为首选方法。
💡 以下，除“表继承”一节，所有示例均未声明式分区
以下的例子均基于下表：
postgres=# select * from empsalary;
id | depname | empno | salary | enroll_date
----+-----------+-------+--------+-------------
10 | develop | 11 | 5200 | 2007-08-01
11 | develop | 7 | 4200 | 2006-06-01
12 | develop | 9 | 4500 | 2008-01-01
13 | develop | 8 | 6000 | 2006-10-01
14 | develop | 10 | 5200 | 2006-08-01
15 | personnel | 5 | 3500 | 2007-12-10
16 | personnel | 2 | 3900 | 2006-10-15
17 | sales | 3 | 4800 | 2007-08-01
18 | sales | 1 | 5000 | 2006-10-01
19 | sales | 4 | 4800 | 2007-08-01
(10 rows)
范围分区
💡 PostgreSQL 的分区表必须在创建时就定义分区策略，不能通过 ALTER TABLE 直接修改表的分区结构。
CREATE TABLE emp (
id integer,
depname TEXT,
empno INTEGER,
salary INTEGER,
enroll_date DATE
) partition by range(enroll_date);
💡 id 列没有定义为主键，因为报错如下：
ERROR: unique constraint on partitioned table must include all partitioning columns
DETAIL: PRIMARY KEY constraint on table "emp" lacks column "enroll_date" which is part of the partition key.
最初插入数据报错，因为分区尚不存在：
postgres=# insert into emp select * from empsalary;
ERROR: no partition of relation "emp" found for row
DETAIL: Partition key of the failing row contains (enroll_date) = (2007-08-01).
创建三个分区：
CREATE TABLE emp_2006 PARTITION OF emp
FOR VALUES FROM ('2006-01-01') TO ('2006-12-31');
CREATE TABLE emp_2007 PARTITION OF emp
FOR VALUES FROM ('2007-01-01') TO ('2007-12-31');
CREATE TABLE emp_2008 PARTITION OF emp
FOR VALUES FROM ('2008-01-01') TO ('2008-12-31');
插入数据成功了，分区也正确：
postgres=# insert into emp select * from empsalary;
INSERT 0 10
postgres=# select * from emp_2006;
id | depname | empno | salary | enroll_date
----+-----------+-------+--------+-------------
11 | develop | 7 | 4200 | 2006-06-01
13 | develop | 8 | 6000 | 2006-10-01
14 | develop | 10 | 5200 | 2006-08-01
16 | personnel | 2 | 3900 | 2006-10-15
18 | sales | 1 | 5000 | 2006-10-01
(5 rows)
postgres=# select * from emp_2007;
id | depname | empno | salary | enroll_date
----+-----------+-------+--------+-------------
10 | develop | 11 | 5200 | 2007-08-01
15 | personnel | 5 | 3500 | 2007-12-10
17 | sales | 3 | 4800 | 2007-08-01
19 | sales | 4 | 4800 | 2007-08-01
(4 rows)
postgres=# select * from emp_2008;
id | depname | empno | salary | enroll_date
----+---------+-------+--------+-------------
12 | develop | 9 | 4500 | 2008-01-01
(1 row)
可以定义默认分区：
postgres=# insert into emp values(20, 'develop', 11, 5200, DATE '2025-06-21');
ERROR: no partition of relation "emp" found for row
DETAIL: Partition key of the failing row contains (enroll_date) = (2025-06-21).
postgres=# CREATE TABLE emp_default PARTITION OF emp DEFAULT;
CREATE TABLE
postgres=# insert into emp values(20, 'develop', 11, 5200, DATE '2025-06-21');
INSERT 0 1
postgres=# select * from emp_default;
id | depname | empno | salary | enroll_date
----+---------+-------+--------+-------------
20 | develop | 11 | 5200 | 2025-06-21
(1 row)
查看分区：
postgres=# \d+ emp
Partitioned table "public.emp"
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
-------------+---------+-----------+----------+---------+----------+-------------+--------------+-------------
id | integer | | | | plain | | |
depname | text | | | | extended | | |
empno | integer | | | | plain | | |
salary | integer | | | | plain | | |
enroll_date | date | | | | plain | | |
Partition key: RANGE (enroll_date)
Partitions: emp_2006 FOR VALUES FROM ('2006-01-01') TO ('2006-12-31'),
emp_2007 FOR VALUES FROM ('2007-01-01') TO ('2007-12-31'),
emp_2008 FOR VALUES FROM ('2008-01-01') TO ('2008-12-31'),
emp_default DEFAULT
💡 分区表是逻辑表，而具体的分区则是物理表。
以下为证明，首先detach分区：
alter table emp detach partition emp_2006;
alter table emp detach partition emp_2007;
alter table emp detach partition emp_2008;
alter table emp detach partition emp_default;
此时emp没有数据了，数据全在分区表中：
postgres=# select * from emp;
id | depname | empno | salary | enroll_date
----+---------+-------+--------+-------------
(0 rows)
postgres=# select * from emp_2006;
id | depname | empno | salary | enroll_date
----+-----------+-------+--------+-------------
11 | develop | 7 | 4200 | 2006-06-01
13 | develop | 8 | 6000 | 2006-10-01
14 | develop | 10 | 5200 | 2006-08-01
16 | personnel | 2 | 3900 | 2006-10-15
18 | sales | 1 | 5000 | 2006-10-01
(5 rows)
此时，我们也可以将分区逐个加回去：
alter table emp attach partition emp_2006 FOR VALUES FROM ('2006-01-01') TO ('2006-12-31');
alter table emp attach partition emp_2007 FOR VALUES FROM ('2007-01-01') TO ('2007-12-31');
alter table emp attach partition emp_2008 FOR VALUES FROM ('2008-01-01') TO ('2008-12-31');
alter table emp attach partition emp_default DEFAULT;
此时数据就都有了。
postgres=# select * from emp;
id | depname | empno | salary | enroll_date
----+-----------+-------+--------+-------------
11 | develop | 7 | 4200 | 2006-06-01
13 | develop | 8 | 6000 | 2006-10-01
14 | develop | 10 | 5200 | 2006-08-01
16 | personnel | 2 | 3900 | 2006-10-15
18 | sales | 1 | 5000 | 2006-10-01
10 | develop | 11 | 5200 | 2007-08-01
15 | personnel | 5 | 3500 | 2007-12-10
17 | sales | 3 | 4800 | 2007-08-01
19 | sales | 4 | 4800 | 2007-08-01
12 | develop | 9 | 4500 | 2008-01-01
20 | develop | 11 | 5200 | 2025-06-21
(11 rows)
列表分区
创建分区：
drop table emp;
CREATE TABLE emp (
id integer,
depname TEXT,
empno INTEGER,
salary INTEGER,
enroll_date DATE
) partition by list(depname);
create table emp_dev partition of emp for values in ('develop');
create table emp_psnl partition of emp for values in ('personnel');
create table emp_sales partition of emp for values in ('sales');
create table emp_default partition of emp default;
查看分区：
postgres=# \d+ emp;
Partitioned table "public.emp"
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
-------------+---------+-----------+----------+---------+----------+-------------+--------------+-------------
id | integer | | | | plain | | |
depname | text | | | | extended | | |
empno | integer | | | | plain | | |
salary | integer | | | | plain | | |
enroll_date | date | | | | plain | | |
Partition key: LIST (depname)
Partitions: emp_dev FOR VALUES IN ('develop'),
emp_psnl FOR VALUES IN ('personnel'),
emp_sales FOR VALUES IN ('sales'),
emp_default DEFAULT
插入数据：
postgres=# insert into emp select * from empsalary;
INSERT 0 10
postgres=# select * from emp;
id | depname | empno | salary | enroll_date
----+-----------+-------+--------+-------------
10 | develop | 11 | 5200 | 2007-08-01
11 | develop | 7 | 4200 | 2006-06-01
12 | develop | 9 | 4500 | 2008-01-01
13 | develop | 8 | 6000 | 2006-10-01
14 | develop | 10 | 5200 | 2006-08-01
15 | personnel | 5 | 3500 | 2007-12-10
16 | personnel | 2 | 3900 | 2006-10-15
17 | sales | 3 | 4800 | 2007-08-01
18 | sales | 1 | 5000 | 2006-10-01
19 | sales | 4 | 4800 | 2007-08-01
(10 rows)
postgres=# select * from emp_sales;
id | depname | empno | salary | enroll_date
----+---------+-------+--------+-------------
17 | sales | 3 | 4800 | 2007-08-01
18 | sales | 1 | 5000 | 2006-10-01
19 | sales | 4 | 4800 | 2007-08-01
(3 rows)
postgres=# insert into emp values(20, 'foobar', 11, 5200, DATE '2025-06-21');
INSERT 0 1
postgres=# select * from emp_default;
id | depname | empno | salary | enroll_date
----+---------+-------+--------+-------------
20 | foobar | 11 | 5200 | 2025-06-21
(1 row)
哈希分区
删除分区表时，附带的物理分区也一并删除。
postgres=# drop table emp;
DROP TABLE
postgres=# select * from emp_sales;
ERROR: relation "emp_sales" does not exist
LINE 1: select * from emp_sales;
^
创建哈希分区：
CREATE TABLE emp (
id integer,
depname TEXT,
empno INTEGER,
salary INTEGER,
enroll_date DATE
) partition by hash(id);
create table emp_p1
partition of emp
for values with (modulus 4, remainder 0);
create table emp_p2
partition of emp
for values with (modulus 4, remainder 1);
create table emp_p3
partition of emp
for values with (modulus 4, remainder 2);
create table emp_p4
partition of emp
for values with (modulus 4, remainder 3);
查看分区：
postgres=# \d+ emp
Partitioned table "public.emp"
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
-------------+---------+-----------+----------+---------+----------+-------------+--------------+-------------
id | integer | | | | plain | | |
depname | text | | | | extended | | |
empno | integer | | | | plain | | |
salary | integer | | | | plain | | |
enroll_date | date | | | | plain | | |
Partition key: HASH (id)
Partitions: emp_p1 FOR VALUES WITH (modulus 4, remainder 0),
emp_p2 FOR VALUES WITH (modulus 4, remainder 1),
emp_p3 FOR VALUES WITH (modulus 4, remainder 2),
emp_p4 FOR VALUES WITH (modulus 4, remainder 3)
插入并查看数据：
postgres=# insert into emp select * from empsalary;
INSERT 0 10
postgres=# select * from emp_p1;
id | depname | empno | salary | enroll_date
----+-----------+-------+--------+-------------
12 | develop | 9 | 4500 | 2008-01-01
14 | develop | 10 | 5200 | 2006-08-01
16 | personnel | 2 | 3900 | 2006-10-15
17 | sales | 3 | 4800 | 2007-08-01
(4 rows)
postgres=# select * from emp_p2;
id | depname | empno | salary | enroll_date
----+---------+-------+--------+-------------
11 | develop | 7 | 4200 | 2006-06-01
19 | sales | 4 | 4800 | 2007-08-01
(2 rows)
postgres=# select * from emp_p3;
id | depname | empno | salary | enroll_date
----+---------+-------+--------+-------------
13 | develop | 8 | 6000 | 2006-10-01
18 | sales | 1 | 5000 | 2006-10-01
(2 rows)
postgres=# select * from emp_p4;
id | depname | empno | salary | enroll_date
----+-----------+-------+--------+-------------
10 | develop | 11 | 5200 | 2007-08-01
15 | personnel | 5 | 3500 | 2007-12-10
(2 rows)
💡 哈希分区表中的空值始终转到余数为 0 的分区。
表继承
继承是面向对象的概念。PG的表继承文档和示例参考这里。
这是不用表继承时的设计：
CREATE TABLE capitals (
name text,
population real,
elevation int, -- (in ft)
state char(2)
);
CREATE TABLE non_capitals (
name text,
population real,
elevation int -- (in ft)
);
CREATE VIEW cities AS
SELECT name, population, elevation FROM capitals
UNION
SELECT name, population, elevation FROM non_capitals;
改用表继承后：
CREATE TABLE cities (
name text,
population real,
elevation int -- (in ft)
);
CREATE TABLE capitals (
state char(2) UNIQUE NOT NULL
) INHERITS (cities);
INSERT INTO cities (name, population, elevation) VALUES
('San Francisco', 870000, 52),
('Los Angeles', 4000000, 305),
('San Diego', 1400000, 62),
('Seattle', 750000, 520),
('Portland', 650000, 50),
('Las Vegas', 640000, 2001),
('Miami', 470000, 6),
('Dallas', 1350000, 430),
('Chicago', 2700000, 594),
('New York City', 8400000, 33),
('Houston', 2300000, 80),
('Orlando', 280000, 82);
查看表继承：
postgres=# \d+ cities;
Table "public.cities"
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
------------+---------+-----------+----------+---------+----------+-------------+--------------+-------------
name | text | | | | extended | | |
population | real | | | | plain | | |
elevation | integer | | | | plain | | |
Child tables: capitals
Access method: heap
postgres=# \d+ capitals
Table "public.capitals"
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
------------+--------------+-----------+----------+---------+----------+-------------+--------------+-------------
name | text | | | | extended | | |
population | real | | | | plain | | |
elevation | integer | | | | plain | | |
state | character(2) | | not null | | extended | | |
Indexes:
"capitals_state_key" UNIQUE CONSTRAINT, btree (state)
Inherits: cities
Access method: heap
查看数据：
postgres=# select * from capitals;
name | population | elevation | state
------+------------+-----------+-------
(0 rows)
postgres=# select * from cities;
name | population | elevation
---------------+------------+-----------
San Francisco | 870000 | 52
Los Angeles | 4e+06 | 305
San Diego | 1.4e+06 | 62
Seattle | 750000 | 520
Portland | 650000 | 50
Las Vegas | 640000 | 2001
Miami | 470000 | 6
Dallas | 1.35e+06 | 430
Chicago | 2.7e+06 | 594
New York City | 8.4e+06 | 33
Houston | 2.3e+06 | 80
Orlando | 280000 | 82
(12 rows)
INSERT INTO capitals (name, population, elevation, state) VALUES
('Denver', 700000, 5280, 'CO'),
('Phoenix', 1600000, 1085, 'AZ'),
('Salt Lake City', 200000, 4226, 'UT'),
('Sacramento', 500000, 30, 'CA'),
('Austin', 950000, 489, 'TX'),
('Tallahassee', 190000, 203, 'FL'),
('Boston', 700000, 141, 'MA'),
('Olympia', 52000, 95, 'WA'),
('Salem', 175000, 154, 'OR'),
('Albany', 98000, 200, 'NY');
在子表插入的数据也会出现在母表中：
postgres=# select count(*) from cities;
count
-------
22
(1 row)
postgres=# select count(*) from capitals;
count
-------
10
(1 row)
注意FROM ONLY语法：
postgres=# SELECT name, elevation
FROM cities
WHERE elevation > 500;
name | elevation
----------------+-----------
Seattle | 520
Las Vegas | 2001
Chicago | 594
Denver | 5280
Phoenix | 1085
Salt Lake City | 4226
(6 rows)
postgres=# SELECT name, elevation
FROM ONLY cities
WHERE elevation > 500;
name | elevation
-----------+-----------
Seattle | 520
Las Vegas | 2001
Chicago | 594
(3 rows)
清空母表同时也会清空子表：
postgres=# truncate table cities;
TRUNCATE TABLE
postgres=# select count(*) from capitals;
count
-------
0
(1 row)
删除表
postgres=# drop table cities;
ERROR: cannot drop table cities because other objects depend on it
DETAIL: table capitals depends on table cities
HINT: Use DROP ... CASCADE to drop the dependent objects too.
postgres=# drop table cities cascade;
NOTICE: drop cascades to table capitals
DROP TABLE
分区维护
以列表分区为例：
drop table emp;
CREATE TABLE emp (
id integer,
depname TEXT,
empno INTEGER,
salary INTEGER,
enroll_date DATE
) partition by list(depname);
create table emp_dev partition of emp for values in ('develop');
create table emp_psnl partition of emp for values in ('personnel');
create table emp_sales partition of emp for values in ('sales');
create table emp_default partition of emp default;
insert into emp select * from empsalary;
select * from emp;
附加新分区
postgres=# create table emp_marketing partition of emp for values in ('marketing');
CREATE TABLE
postgres=# \d+ emp;
Partitioned table "public.emp"
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
-------------+---------+-----------+----------+---------+----------+-------------+--------------+-------------
id | integer | | | | plain | | |
depname | text | | | | extended | | |
empno | integer | | | | plain | | |
salary | integer | | | | plain | | |
enroll_date | date | | | | plain | | |
Partition key: LIST (depname)
Partitions: emp_dev FOR VALUES IN ('develop'),
emp_marketing FOR VALUES IN ('marketing'),
emp_psnl FOR VALUES IN ('personnel'),
emp_sales FOR VALUES IN ('sales'),
emp_default DEFAULT
分离现有分区
postgres=# select count(*) from emp;
count
-------
10
(1 row)
postgres=# alter table emp detach partition emp_sales;
ALTER TABLE
postgres=# select count(*) from emp;
count
-------
7
(1 row)
postgres=# select * from emp_sales;
id | depname | empno | salary | enroll_date
----+---------+-------+--------+-------------
17 | sales | 3 | 4800 | 2007-08-01
18 | sales | 1 | 5000 | 2006-10-01
19 | sales | 4 | 4800 | 2007-08-01
(3 rows)
将现有表附加到父表
postgres=# alter table emp attach partition emp_sales for values in ('sales');
ALTER TABLE
postgres=# select count(*) from emp;
count
-------
10
(1 row)
默认分区
列表分区和范围分区可以有默认分区，哈希分区则无。
分区和表空间
先创建目录。
sudo mkdir -p /data/tbs
sudo chown -R postgres:postgres /data/tbs
cd /data/tbs
mkdir ts_a
mkdir ts_b
创建表空间：
postgres=# create tablespace ts_a location '/data/tbs/ts_a';
CREATE TABLESPACE
postgres=# create tablespace ts_b location '/data/tbs/ts_b';
CREATE TABLESPACE
postgres=# \db
List of tablespaces
Name | Owner | Location
------------+----------+----------------
pg_default | postgres |
pg_global | postgres |
ts_a | postgres | /data/tbs/ts_a
ts_b | postgres | /data/tbs/ts_b
(4 rows)
关键就是，创建分区时可以指定表空间：
CREATE TABLE emp (
id integer,
depname TEXT,
empno INTEGER,
salary INTEGER,
enroll_date DATE
) partition by range(enroll_date);
CREATE TABLE emp_2006 PARTITION OF emp
FOR VALUES FROM ('2006-01-01') TO ('2007-12-31') tablespace ts_a;
CREATE TABLE emp_2008 PARTITION OF emp
FOR VALUES FROM ('2008-01-01') TO ('2008-12-31') tablespace ts_b;
CREATE TABLE emp_default PARTITION OF emp DEFAULT;
insert into emp select * from empsalary;
探索文件系统：
$ cd /data/tbs/ts_a/PG_16_202307071/5
$ oid2name
All databases:
Oid Database Name Tablespace
--------------------------------
5 postgres pg_default
4 template0 pg_default
1 template1 pg_default
$ ls
16919 16922 16923
$ oid2name -o 16919
From database "postgres":
Filenode Table Name
----------------------
16919 emp_2006
$ oid2name -o 16922
From database "postgres":
Filenode Table Name
--------------------------
16922 pg_toast_16919
$ oid2name -o 16923
From database "postgres":
Filenode Table Name
--------------------------------
16923 pg_toast_16919_index
简单案例研究
从本书示例中导入一个数据库：
git clone https://github.com/PacktPublishing/Learn-PostgreSQL-Second-Edition.git
cd Learn-PostgreSQL-Second-Edition/CHAPTER_09/code
gunzip < backup-db-world-temperatures.sql.gz | psql
实际导入的是一个database dump，也就是一个SQL文件：
--
-- PostgreSQL database dump
--
-- Dumped from database version 15.2 (Debian 15.2-1.pgdg110+1)
-- Dumped by pg_dump version 15.2 (Debian 15.2-1.pgdg110+1)
SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;
--
-- Name: world_temperatures; Type: DATABASE; Schema: -; Owner: postgres
--
CREATE DATABASE world_temperatures WITH TEMPLATE = template0 ENCODING = 'UTF8' LOCALE_PROVIDER = libc LOCALE = 'en_US.utf8';
ALTER DATABASE world_temperatures OWNER TO postgres;
\connect world_temperatures
...
查看数据库：
postgres=# \x
Expanded display is on.
postgres=# \l+ world_temperatures
List of databases
-[ RECORD 1 ]-----+-------------------
Name | world_temperatures
Owner | postgres
Encoding | UTF8
Locale Provider | libc
Collate | en_US.utf8
Ctype | en_US.utf8
ICU Locale |
ICU Rules |
Access privileges |
Size | 119 MB
Tablespace | pg_default
Description |
查看表：
postgres=# \c world_temperatures
world_temperatures=# \d+ basilea
Table "public.basilea"
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
-------------+--------------------------+-----------+----------+-------------------------------------+---------+-------------+--------------+-------------
insert_time | timestamp with time zone | | not null | | plain | | |
temperature | numeric(8,6) | | | | main | | |
id | integer | | not null | nextval('basilea_id_seq'::regclass) | plain | | |
Indexes:
"basilea_temp_pkey" PRIMARY KEY, btree (id, insert_time)
Access method: heap
world_temperatures=# \d+ basilea_partitioned
Partitioned table "public.basilea_partitioned"
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
-------------+--------------------------+-----------+----------+-------------------------------------------------+---------+-------------+--------------+-------------
id | integer | | not null | nextval('basilea_partitioned_id_seq'::regclass) | plain | | |
insert_time | timestamp with time zone | | not null | | plain | | |
temperature | numeric(8,6) | | | | main | | |
Partition key: RANGE (insert_time)
Indexes:
"basilea_partitioned_pkey" PRIMARY KEY, btree (id, insert_time)
Partitions: basilea_partitioned_1950 FOR VALUES FROM ('1949-12-31 23:00:00+00') TO ('1950-12-31 23:00:00+00'),
basilea_partitioned_1951 FOR VALUES FROM ('1950-12-31 23:00:00+00') TO ('1951-12-31 23:00:00+00'),
basilea_partitioned_1952 FOR VALUES FROM ('1951-12-31 23:00:00+00') TO ('1952-12-31 23:00:00+00'),
basilea_partitioned_1953 FOR VALUES FROM ('1952-12-31 23:00:00+00') TO ('1953-12-31 23:00:00+00'),
basilea_partitioned_1954 FOR VALUES FROM ('1953-12-31 23:00:00+00') TO ('1954-12-31 23:00:00+00'),
basilea_partitioned_1955 FOR VALUES FROM ('1954-12-31 23:00:00+00') TO ('1955-12-31 23:00:00+00'),
basilea_partitioned_1956 FOR VALUES FROM ('1955-12-31 23:00:00+00') TO ('1956-12-31 23:00:00+00'),
basilea_partitioned_1957 FOR VALUES FROM ('1956-12-31 23:00:00+00') TO ('1957-12-31 23:00:00+00'),
basilea_partitioned_1958 FOR VALUES FROM ('1957-12-31 23:00:00+00') TO ('1958-12-31 23:00:00+00'),
basilea_partitioned_1959 FOR VALUES FROM ('1958-12-31 23:00:00+00') TO ('1959-12-31 23:00:00+00'),
basilea_partitioned_1960 FOR VALUES FROM ('1959-12-31 23:00:00+00') TO ('1960-12-31 23:00:00+00'),
basilea_partitioned_1961 FOR VALUES FROM ('1960-12-31 23:00:00+00') TO ('1961-12-31 23:00:00+00'),
basilea_partitioned_1962 FOR VALUES FROM ('1961-12-31 23:00:00+00') TO ('1962-12-31 23:00:00+00'),
basilea_partitioned_1963 FOR VALUES FROM ('1962-12-31 23:00:00+00') TO ('1963-12-31 23:00:00+00'),
basilea_partitioned_1964 FOR VALUES FROM ('1963-12-31 23:00:00+00') TO ('1964-12-31 23:00:00+00'),
basilea_partitioned_1965 FOR VALUES FROM ('1964-12-31 23:00:00+00') TO ('1965-12-31 23:00:00+00'),
basilea_partitioned_1966 FOR VALUES FROM ('1965-12-31 23:00:00+00') TO ('1966-12-31 23:00:00+00'),
basilea_partitioned_1967 FOR VALUES FROM ('1966-12-31 23:00:00+00') TO ('1967-12-31 23:00:00+00'),
basilea_partitioned_1968 FOR VALUES FROM ('1967-12-31 23:00:00+00') TO ('1968-12-31 23:00:00+00'),
basilea_partitioned_1969 FOR VALUES FROM ('1968-12-31 23:00:00+00') TO ('1969-12-31 23:00:00+00'),
basilea_partitioned_1970 FOR VALUES FROM ('1969-12-31 23:00:00+00') TO ('1970-12-31 23:00:00+00'),
basilea_partitioned_1971 FOR VALUES FROM ('1970-12-31 23:00:00+00') TO ('1971-12-31 23:00:00+00'),
basilea_partitioned_1972 FOR VALUES FROM ('1971-12-31 23:00:00+00') TO ('1972-12-31 23:00:00+00'),
basilea_partitioned_1973 FOR VALUES FROM ('1972-12-31 23:00:00+00') TO ('1973-12-31 23:00:00+00'),
basilea_partitioned_1974 FOR VALUES FROM ('1973-12-31 23:00:00+00') TO ('1974-12-31 23:00:00+00'),
basilea_partitioned_1975 FOR VALUES FROM ('1974-12-31 23:00:00+00') TO ('1975-12-31 23:00:00+00'),
basilea_partitioned_1976 FOR VALUES FROM ('1975-12-31 23:00:00+00') TO ('1976-12-31 23:00:00+00'),
basilea_partitioned_1977 FOR VALUES FROM ('1976-12-31 23:00:00+00') TO ('1977-12-31 23:00:00+00'),
basilea_partitioned_1978 FOR VALUES FROM ('1977-12-31 23:00:00+00') TO ('1978-12-31 23:00:00+00'),
basilea_partitioned_1979 FOR VALUES FROM ('1978-12-31 23:00:00+00') TO ('1979-12-31 23:00:00+00'),
basilea_partitioned_1980 FOR VALUES FROM ('1979-12-31 23:00:00+00') TO ('1980-12-31 23:00:00+00'),
basilea_partitioned_1981 FOR VALUES FROM ('1980-12-31 23:00:00+00') TO ('1981-12-31 23:00:00+00'),
basilea_partitioned_1982 FOR VALUES FROM ('1981-12-31 23:00:00+00') TO ('1982-12-31 23:00:00+00'),
basilea_partitioned_1983 FOR VALUES FROM ('1982-12-31 23:00:00+00') TO ('1983-12-31 23:00:00+00'),
basilea_partitioned_1984 FOR VALUES FROM ('1983-12-31 23:00:00+00') TO ('1984-12-31 23:00:00+00'),
basilea_partitioned_1985 FOR VALUES FROM ('1984-12-31 23:00:00+00') TO ('1985-12-31 23:00:00+00'),
basilea_partitioned_1986 FOR VALUES FROM ('1985-12-31 23:00:00+00') TO ('1986-12-31 23:00:00+00'),
basilea_partitioned_1987 FOR VALUES FROM ('1986-12-31 23:00:00+00') TO ('1987-12-31 23:00:00+00'),
basilea_partitioned_1988 FOR VALUES FROM ('1987-12-31 23:00:00+00') TO ('1988-12-31 23:00:00+00'),
basilea_partitioned_1989 FOR VALUES FROM ('1988-12-31 23:00:00+00') TO ('1989-12-31 23:00:00+00'),
basilea_partitioned_1990 FOR VALUES FROM ('1989-12-31 23:00:00+00') TO ('1990-12-31 23:00:00+00'),
basilea_partitioned_1991 FOR VALUES FROM ('1990-12-31 23:00:00+00') TO ('1991-12-31 23:00:00+00'),
basilea_partitioned_1992 FOR VALUES FROM ('1991-12-31 23:00:00+00') TO ('1992-12-31 23:00:00+00'),
basilea_partitioned_1993 FOR VALUES FROM ('1992-12-31 23:00:00+00') TO ('1993-12-31 23:00:00+00'),
basilea_partitioned_1994 FOR VALUES FROM ('1993-12-31 23:00:00+00') TO ('1994-12-31 23:00:00+00'),
basilea_partitioned_1995 FOR VALUES FROM ('1994-12-31 23:00:00+00') TO ('1995-12-31 23:00:00+00'),
basilea_partitioned_1996 FOR VALUES FROM ('1995-12-31 23:00:00+00') TO ('1996-12-31 23:00:00+00'),
basilea_partitioned_1997 FOR VALUES FROM ('1996-12-31 23:00:00+00') TO ('1997-12-31 23:00:00+00'),
basilea_partitioned_1998 FOR VALUES FROM ('1997-12-31 23:00:00+00') TO ('1998-12-31 23:00:00+00'),
basilea_partitioned_1999 FOR VALUES FROM ('1998-12-31 23:00:00+00') TO ('1999-12-31 23:00:00+00'),
basilea_partitioned_2000 FOR VALUES FROM ('1999-12-31 23:00:00+00') TO ('2000-12-31 23:00:00+00'),
basilea_partitioned_2001 FOR VALUES FROM ('2000-12-31 23:00:00+00') TO ('2001-12-31 23:00:00+00'),
basilea_partitioned_2002 FOR VALUES FROM ('2001-12-31 23:00:00+00') TO ('2002-12-31 23:00:00+00'),
basilea_partitioned_2003 FOR VALUES FROM ('2002-12-31 23:00:00+00') TO ('2003-12-31 23:00:00+00'),
basilea_partitioned_2004 FOR VALUES FROM ('2003-12-31 23:00:00+00') TO ('2004-12-31 23:00:00+00'),
basilea_partitioned_2005 FOR VALUES FROM ('2004-12-31 23:00:00+00') TO ('2005-12-31 23:00:00+00'),
basilea_partitioned_2006 FOR VALUES FROM ('2005-12-31 23:00:00+00') TO ('2006-12-31 23:00:00+00'),
basilea_partitioned_2007 FOR VALUES FROM ('2006-12-31 23:00:00+00') TO ('2007-12-31 23:00:00+00'),
basilea_partitioned_2008 FOR VALUES FROM ('2007-12-31 23:00:00+00') TO ('2008-12-31 23:00:00+00'),
basilea_partitioned_2009 FOR VALUES FROM ('2008-12-31 23:00:00+00') TO ('2009-12-31 23:00:00+00'),
basilea_partitioned_2010 FOR VALUES FROM ('2009-12-31 23:00:00+00') TO ('2010-12-31 23:00:00+00'),
basilea_partitioned_2011 FOR VALUES FROM ('2010-12-31 23:00:00+00') TO ('2011-12-31 23:00:00+00'),
basilea_partitioned_2012 FOR VALUES FROM ('2011-12-31 23:00:00+00') TO ('2012-12-31 23:00:00+00'),
basilea_partitioned_2013 FOR VALUES FROM ('2012-12-31 23:00:00+00') TO ('2013-12-31 23:00:00+00'),
basilea_partitioned_2014 FOR VALUES FROM ('2013-12-31 23:00:00+00') TO ('2014-12-31 23:00:00+00'),
basilea_partitioned_2015 FOR VALUES FROM ('2014-12-31 23:00:00+00') TO ('2015-12-31 23:00:00+00'),
basilea_partitioned_2016 FOR VALUES FROM ('2015-12-31 23:00:00+00') TO ('2016-12-31 23:00:00+00'),
basilea_partitioned_2017 FOR VALUES FROM ('2016-12-31 23:00:00+00') TO ('2017-12-31 23:00:00+00'),
basilea_partitioned_2018 FOR VALUES FROM ('2017-12-31 23:00:00+00') TO ('2018-12-31 23:00:00+00'),
basilea_partitioned_2019 FOR VALUES FROM ('2018-12-31 23:00:00+00') TO ('2019-12-31 23:00:00+00'),
basilea_partitioned_2020 FOR VALUES FROM ('2019-12-31 23:00:00+00') TO ('2020-12-31 23:00:00+00'),
basilea_partitioned_2021 FOR VALUES FROM ('2020-12-31 23:00:00+00') TO ('2021-12-31 23:00:00+00'),
basilea_partitioned_2022 FOR VALUES FROM ('2021-12-31 23:00:00+00') TO ('2022-12-31 23:00:00+00'),
basilea_partitioned_2023 FOR VALUES FROM ('2022-12-31 23:00:00+00') TO ('2023-12-31 23:00:00+00'),
basilea_partitioned_default DEFAULT
这两个表basilea和basilea_partitioned记录数是一样的，前者未分区，后者范围分区。
以下是实验部分：
-- 获取平均温度最低的5年
world_temperatures=# select extract (year from insert_time) as year, avg(temperature) avg_temp from basilea group by 1 order by 2 limit 5;
year | avg_temp
------+--------------------
1956 | 8.8073832344034608
1963 | 8.9077977708904110
1980 | 9.3459840948315118
1969 | 9.3705488990867580
1972 | 9.3749401615437158
(5 rows)
-- 获取平均温度最高的5年
world_temperatures=# select extract (year from insert_time) as year, avg(temperature) avg_temp from basilea group by 1 order by 2 desc limit 5;
year | avg_temp
------+---------------------
2022 | 12.7320820592465753
2018 | 12.5638742964611872
2020 | 12.3662106902322404
2014 | 12.0601722329908676
2015 | 11.9246379973744292
(5 rows)
比较执行计划，先未分区表，再分区表：
world_temperatures=# explain analyze select extract (year from insert_time) as year, avg(temperature) avg_temp from basilea group by 1 order by 2 desc limit 5;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=98297.21..98297.23 rows=5 width=64) (actual time=1703.717..1703.721 rows=5 loops=1)
-> Sort (cost=98297.21..99896.99 rows=639912 width=64) (actual time=1703.712..1703.714 rows=5 loops=1)
Sort Key: (avg(temperature)) DESC
Sort Method: top-N heapsort Memory: 25kB
-> HashAggregate (cost=68071.20..87668.51 rows=639912 width=64) (actual time=1703.046..1703.577 rows=73 loops=1)
Group Key: EXTRACT(year FROM insert_time)
Planned Partitions: 32 Batches: 1 Memory Usage: 817kB
-> Seq Scan on basilea (cost=0.00..12078.90 rows=639912 width=40) (actual time=0.024..871.644 rows=639912 loops=1)
Planning Time: 0.161 ms
Execution Time: 1703.848 ms
(10 rows)
world_temperatures=# explain analyze select extract (year from insert_time) as year, avg(temperature) avg_temp from basilea_partitioned group by 1 order by 2 desc limit 5;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------
Limit (cost=13183.59..13183.61 rows=5 width=64) (actual time=1074.662..1076.907 rows=5 loops=1)
-> Sort (cost=13183.59..13184.09 rows=200 width=64) (actual time=1074.661..1076.904 rows=5 loops=1)
Sort Key: (avg(basilea_partitioned.temperature)) DESC
Sort Method: top-N heapsort Memory: 25kB
-> Finalize GroupAggregate (cost=13127.60..13180.27 rows=200 width=64) (actual time=1073.887..1076.790 rows=73 loops=1)
Group Key: (EXTRACT(year FROM basilea_partitioned.insert_time))
-> Gather Merge (cost=13127.60..13174.27 rows=400 width=64) (actual time=1073.869..1076.278 rows=127 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Sort (cost=12127.58..12128.08 rows=200 width=64) (actual time=1045.321..1045.352 rows=42 loops=3)
Sort Key: (EXTRACT(year FROM basilea_partitioned.insert_time))
Sort Method: quicksort Memory: 29kB
Worker 0: Sort Method: quicksort Memory: 28kB
Worker 1: Sort Method: quicksort Memory: 28kB
-> Partial HashAggregate (cost=12116.93..12119.93 rows=200 width=64) (actual time=1045.148..1045.235 rows=42 loops=3)
Group Key: (EXTRACT(year FROM basilea_partitioned.insert_time))
Batches: 1 Memory Usage: 64kB
Worker 0: Batches: 1 Memory Usage: 48kB
Worker 1: Batches: 1 Memory Usage: 48kB
-> Parallel Append (cost=0.00..10780.64 rows=267259 width=40) (actual time=0.025..582.302 rows=213304 loops=3)
-> Parallel Seq Scan on basilea_partitioned_1952 basilea_partitioned_3 (cost=0.00..129.59 rows=5167 width=40) (actual time=0.029..2
0.344 rows=8784 loops=1)
-> Parallel Seq Scan on basilea_partitioned_1956 basilea_partitioned_7 (cost=0.00..129.59 rows=5167 width=40) (actual time=0.025..1
3.299 rows=8784 loops=1)
...
13.165 rows=8760 loops=1)
-> Parallel Seq Scan on basilea_partitioned_2021 basilea_partitioned_72 (cost=0.00..128.41 rows=5153 width=40) (actual time=0.008..
13.341 rows=8760 loops=1)
-> Parallel Seq Scan on basilea_partitioned_default basilea_partitioned_75 (cost=0.00..21.10 rows=888 width=40) (actual time=0.001.
.0.001 rows=0 loops=1)
-> Parallel Seq Scan on basilea_partitioned_2023 basilea_partitioned_74 (cost=0.00..1.01 rows=1 width=40) (actual time=0.015..0.016
rows=1 loops=1)
Planning Time: 5.404 ms
Execution Time: 1077.542 ms
(97 rows)
比较另一个SQL在两张表上的表现：
world_temperatures=# explain analyze select extract (year from insert_time) as year, avg(temperature) avg_temp from basilea where insert_time >='2021-01-01' and insert_time < '2023-01-01' group by 1 order by 2 desc limit 5;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------
Limit (cost=11298.03..11298.04 rows=5 width=64) (actual time=172.644..174.901 rows=2 loops=1)
-> Sort (cost=11298.03..11340.51 rows=16991 width=64) (actual time=172.643..174.898 rows=2 loops=1)
Sort Key: (avg(temperature)) DESC
Sort Method: quicksort Memory: 25kB
-> Finalize HashAggregate (cost=10760.95..11015.82 rows=16991 width=64) (actual time=172.451..174.888 rows=2 loops=1)
Group Key: (EXTRACT(year FROM insert_time))
Batches: 1 Memory Usage: 793kB
-> Gather (cost=9132.55..10654.75 rows=14160 width=64) (actual time=172.019..174.351 rows=6 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Partial HashAggregate (cost=8132.55..8238.75 rows=7080 width=64) (actual time=157.460..157.547 rows=2 loops=3)
Group Key: EXTRACT(year FROM insert_time)
Batches: 1 Memory Usage: 217kB
Worker 0: Batches: 1 Memory Usage: 217kB
Worker 1: Batches: 1 Memory Usage: 217kB
-> Parallel Seq Scan on basilea (cost=0.00..8097.15 rows=7080 width=40) (actual time=128.935..141.299 rows=5840 loops=3)
Filter: ((insert_time >= '2021-01-01 00:00:00+00'::timestamp with time zone) AND (insert_time < '2023-01-01 00:00:00+00'::timestamp with ti
me zone))
Rows Removed by Filter: 207464
Planning Time: 0.212 ms
Execution Time: 175.335 ms
(20 rows)
world_temperatures=# explain analyze select extract (year from insert_time) as year, avg(temperature) avg_temp from basilea_partitioned where insert_time >='2021-01-01' and insert_time < '2023-01-01' group by 1 order by 2 desc limit 5;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----
Limit (cost=618.10..618.11 rows=5 width=64) (actual time=59.202..59.206 rows=2 loops=1)
-> Sort (cost=618.10..618.60 rows=200 width=64) (actual time=59.201..59.203 rows=2 loops=1)
Sort Key: (avg(basilea_partitioned.temperature)) DESC
Sort Method: quicksort Memory: 25kB
-> HashAggregate (cost=611.78..614.78 rows=200 width=64) (actual time=59.189..59.195 rows=2 loops=1)
Group Key: (EXTRACT(year FROM basilea_partitioned.insert_time))
Batches: 1 Memory Usage: 40kB
-> Append (cost=0.00..524.19 rows=17517 width=40) (actual time=0.025..34.504 rows=17520 loops=1)
-> Seq Scan on basilea_partitioned_2021 basilea_partitioned_1 (cost=0.00..217.30 rows=8758 width=40) (actual time=0.024..15.104 rows=8759 loops=1)
Filter: ((insert_time >= '2021-01-01 00:00:00+00'::timestamp with time zone) AND (insert_time < '2023-01-01 00:00:00+00'::timestamp with time zon
e))
Rows Removed by Filter: 1
-> Seq Scan on basilea_partitioned_2022 basilea_partitioned_2 (cost=0.00..218.30 rows=8758 width=40) (actual time=0.016..14.747 rows=8760 loops=1)
Filter: ((insert_time >= '2021-01-01 00:00:00+00'::timestamp with time zone) AND (insert_time < '2023-01-01 00:00:00+00'::timestamp with time zon
e))
-> Seq Scan on basilea_partitioned_2023 basilea_partitioned_3 (cost=0.00..1.02 rows=1 width=40) (actual time=0.016..0.017 rows=1 loops=1)
Filter: ((insert_time >= '2021-01-01 00:00:00+00'::timestamp with time zone) AND (insert_time < '2023-01-01 00:00:00+00'::timestamp with time zon
e))
Planning Time: 0.656 ms
Execution Time: 59.273 ms
(17 rows)
查看和分区查询优化相关的设置：
world_temperatures=# \x
Expanded display is on.
world_temperatures=# select * from pg_settings where name ='constraint_exclusion';
-[ RECORD 1 ]---+-----------------------------------------------------------------------------------------
name | constraint_exclusion
setting | partition
unit |
category | Query Tuning / Other Planner Options
short_desc | Enables the planner to use constraints to optimize queries.
extra_desc | Table scans will be skipped if their constraints guarantee that no rows match the query.
context | user
vartype | enum
source | default
min_val |
max_val |
enumvals | {partition,on,off}
boot_val | partition
reset_val | partition
sourcefile |
sourceline |
pending_restart | f
world_temperatures=# \x
Expanded display is off.
验证你的知识
- 是否可以在 PostgreSQL 中执行声明式分区？
- 是否可以按范围对该表进行分区？
- 默认分区的用途是什么？
- 不同的分区是否可以存放在不同的磁盘上？
- PG是否支持复合分区（分区的分区）？
- PG支持分区索引吗？支持类似于oracle的全局索引吗？