本文将讨论PostgreSQL 12版中内置或声明性分区提供的功能。 该演示是为在HighLoad ++ Siberia 2019会议上的同名
演讲准备的（更新：演讲中出现了一个
视频 ）。
所有示例均在最近出现的Beta版本上执行：
=> SELECT version();
version ------------------------------------------------------------------------------------------------------------------ PostgreSQL 12beta1 on i686-pc-linux-gnu, compiled by gcc (Ubuntu 5.4.0-6ubuntu1~16.04.10) 5.4.0 20160609, 32-bit (1 row)
这些示例使用了演示数据库的预订和票务表。 预订表包含从2017年6月到2017年8月三个月的条目，并具有以下结构：
=> \d bookings
Table "bookings.bookings" Column | Type | Collation | Nullable | Default --------------+--------------------------+-----------+----------+--------- book_ref | character(6) | | not null | book_date | timestamp with time zone | | not null | total_amount | numeric(10,2) | | not null | Indexes: "bookings_pkey" PRIMARY KEY, btree (book_ref) Referenced by: TABLE "tickets" CONSTRAINT "tickets_book_ref_fkey" FOREIGN KEY (book_ref) REFERENCES bookings(book_ref)
预订可能包括几张票。 带有票证的表的结构：
=> \d tickets
Table "bookings.tickets" Column | Type | Collation | Nullable | Default ----------------+-----------------------+-----------+----------+--------- ticket_no | character(13) | | not null | book_ref | character(6) | | not null | passenger_id | character varying(20) | | not null | passenger_name | text | | not null | contact_data | jsonb | | | Indexes: "tickets_pkey" PRIMARY KEY, btree (ticket_no) Foreign-key constraints: "tickets_book_ref_fkey" FOREIGN KEY (book_ref) REFERENCES bookings(book_ref) Referenced by: TABLE "ticket_flights" CONSTRAINT "ticket_flights_ticket_no_fkey" FOREIGN KEY (ticket_no) REFERENCES tickets(ticket_no)
该信息应足以理解我们将尝试对表进行分区的示例。
→
在此处了解有关演示库的更多信息
。范围分区
首先，尝试使预订表按日期范围划分。 在这种情况下，将按以下方式创建表：
=> CREATE TABLE bookings_range ( book_ref character(6), book_date timestamptz, total_amount numeric(10,2) ) PARTITION BY RANGE(book_date);
每个月的单独部分：
=> CREATE TABLE bookings_range_201706 PARTITION OF bookings_range FOR VALUES FROM ('2017-06-01'::timestamptz) TO ('2017-07-01'::timestamptz); => CREATE TABLE bookings_range_201707 PARTITION OF bookings_range FOR VALUES FROM ('2017-07-01'::timestamptz) TO ('2017-08-01'::timestamptz);
为了指示部分的边界，不仅可以使用常量，还可以使用表达式，例如函数调用。 表达式的值是在创建节并将其存储在系统目录中时计算的：
=> CREATE TABLE bookings_range_201708 PARTITION OF bookings_range FOR VALUES FROM (to_timestamp('01.08.2017','DD.MM.YYYY')) TO (to_timestamp('01.09.2017','DD.MM.YYYY'));
表格说明：
=> \d+ bookings_range
Partitioned table "bookings.bookings_range" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description --------------+--------------------------+-----------+----------+---------+----------+--------------+------------- book_ref | character(6) | | | | extended | | book_date | timestamp with time zone | | | | plain | | total_amount | numeric(10,2) | | | | main | | Partition key: RANGE (book_date) Partitions: bookings_range_201706 FOR VALUES FROM ('2017-06-01 00:00:00+03') TO ('2017-07-01 00:00:00+03'), bookings_range_201707 FOR VALUES FROM ('2017-07-01 00:00:00+03') TO ('2017-08-01 00:00:00+03'), bookings_range_201708 FOR VALUES FROM ('2017-08-01 00:00:00+03') TO ('2017-09-01 00:00:00+03')
够了 无需触发器即可插入记录；不需要CHECK约束。 也不需要CONSTRAINT_EXCLUSION参数，您甚至可以将其关闭：
=> SET constraint_exclusion = OFF;
在各部分中填充自动布局：
=> INSERT INTO bookings_range SELECT * FROM bookings;
INSERT 0 262788
声明性语法仍然隐藏继承的表，因此您可以通过查询查看各节中行的分布：
=> SELECT tableoid::regclass, count(*) FROM bookings_range GROUP BY tableoid;
tableoid | count -----------------------+-------- bookings_range_201706 | 7303 bookings_range_201707 | 167062 bookings_range_201708 | 88423 (3 rows)
但是父表中没有数据：
=> SELECT * FROM ONLY bookings_range;
book_ref | book_date | total_amount ----------+-----------+-------------- (0 rows)
检查查询计划中是否包括以下部分：
=> EXPLAIN (COSTS OFF) SELECT * FROM bookings_range WHERE book_date = '2017-07-01'::timestamptz;
QUERY PLAN ---------------------------------------------------------------------------- Seq Scan on bookings_range_201707 Filter: (book_date = '2017-07-01 00:00:00+03'::timestamp with time zone) (2 rows)
按预期只扫描一个部分。
以下示例将to_timestamp函数与STABLE可变性类别一起使用，而不是一个常量：
=> EXPLAIN (COSTS OFF) SELECT * FROM bookings_range WHERE book_date = to_timestamp('01.07.2017','DD.MM.YYYY');
QUERY PLAN ------------------------------------------------------------------------------------ Append Subplans Removed: 2 -> Seq Scan on bookings_range_201707 Filter: (book_date = to_timestamp('01.07.2017'::text, 'DD.MM.YYYY'::text)) (4 rows)
初始化查询计划并将部分部分从视图中排除时（子计划已删除行），将计算该函数的值。
但这仅适用于SELECT。 更改数据时，尚未实现基于STABLE函数的值的节排除：
=> EXPLAIN (COSTS OFF) DELETE FROM bookings_range WHERE book_date = to_timestamp('01.07.2017','DD.MM.YYYY');
QUERY PLAN ------------------------------------------------------------------------------------ Delete on bookings_range Delete on bookings_range_201706 Delete on bookings_range_201707 Delete on bookings_range_201708 -> Seq Scan on bookings_range_201706 Filter: (book_date = to_timestamp('01.07.2017'::text, 'DD.MM.YYYY'::text)) -> Seq Scan on bookings_range_201707 Filter: (book_date = to_timestamp('01.07.2017'::text, 'DD.MM.YYYY'::text)) -> Seq Scan on bookings_range_201708 Filter: (book_date = to_timestamp('01.07.2017'::text, 'DD.MM.YYYY'::text)) (10 rows)
因此，您应该使用常量：
=> EXPLAIN (COSTS OFF) DELETE FROM bookings_range WHERE book_date = '2017-07-01'::timestamptz;
QUERY PLAN ---------------------------------------------------------------------------------- Delete on bookings_range Delete on bookings_range_201707 -> Seq Scan on bookings_range_201707 Filter: (book_date = '2017-07-01 00:00:00+03'::timestamp with time zone) (4 rows)
索引排序
要执行以下查询，需要对从不同部分获得的结果进行排序。 因此，在查询计划中，我们看到SORT节点和该计划的高初始成本：
=> EXPLAIN SELECT * FROM bookings_range ORDER BY book_date;
QUERY PLAN ------------------------------------------------------------------------------------------ Sort (cost=24649.77..25077.15 rows=170952 width=52) Sort Key: bookings_range_201706.book_date -> Append (cost=0.00..4240.28 rows=170952 width=52) -> Seq Scan on bookings_range_201706 (cost=0.00..94.94 rows=4794 width=52) -> Seq Scan on bookings_range_201707 (cost=0.00..2151.30 rows=108630 width=52) -> Seq Scan on bookings_range_201708 (cost=0.00..1139.28 rows=57528 width=52) (6 rows)
在book_date上创建索引。 在每个部分中创建索引，而不是单个全局索引：
=> CREATE INDEX book_date_idx ON bookings_range(book_date);
=> \di bookings_range*
List of relations Schema | Name | Type | Owner | Table ----------+-------------------------------------+-------+---------+----------------------- bookings | bookings_range_201706_book_date_idx | index | student | bookings_range_201706 bookings | bookings_range_201707_book_date_idx | index | student | bookings_range_201707 bookings | bookings_range_201708_book_date_idx | index | student | bookings_range_201708 (3 rows)
现在，以前的排序查询可以使用分区键上的索引，并以排序形式立即从不同部分返回结果。 不需要SORT节点，并且需要最低成本才能生成结果的第一行：
=> EXPLAIN SELECT * FROM bookings_range ORDER BY book_date;
QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------- Append (cost=1.12..14880.88 rows=262788 width=52) -> Index Scan using bookings_range_201706_book_date_idx on bookings_range_201706 (cost=0.28..385.83 rows=7303 width=52) -> Index Scan using bookings_range_201707_book_date_idx on bookings_range_201707 (cost=0.42..8614.35 rows=167062 width=52) -> Index Scan using bookings_range_201708_book_date_idx on bookings_range_201708 (cost=0.42..4566.76 rows=88423 width=52) (4 rows)
集中支持以这种方式创建的分区索引。 添加新部分时，将自动在其上创建索引。 而且您不能删除仅一个部分的索引：
=> DROP INDEX bookings_range_201706_book_date_idx;
ERROR: cannot drop index bookings_range_201706_book_date_idx because index book_date_idx requires it HINT: You can drop index book_date_idx instead.
仅整体上：
=> DROP INDEX book_date_idx;
DROP INDEX
创建索引...同时
在分区表上创建索引时，不能指定CONCURRENTLY。
但是您可以执行以下操作。 首先，我们仅在主表上创建索引，它将收到无效状态：
=> CREATE INDEX book_date_idx ON ONLY bookings_range(book_date);
=> SELECT indisvalid FROM pg_index WHERE indexrelid::regclass::text = 'book_date_idx';
indisvalid ------------ f (1 row)
然后使用CONCURRENTLY选项在所有部分上创建索引：
=> CREATE INDEX CONCURRENTLY book_date_201706_idx ON bookings_range_201706 (book_date); => CREATE INDEX CONCURRENTLY book_date_201707_idx ON bookings_range_201707 (book_date); => CREATE INDEX CONCURRENTLY book_date_201708_idx ON bookings_range_201708 (book_date);
现在我们将本地索引连接到全局：
=> ALTER INDEX book_date_idx ATTACH PARTITION book_date_201706_idx; => ALTER INDEX book_date_idx ATTACH PARTITION book_date_201707_idx; => ALTER INDEX book_date_idx ATTACH PARTITION book_date_201708_idx;
这类似于连接分区表，稍后我们将进行介绍。 一旦连接了所有索引段，主索引将更改其状态：
=> SELECT indisvalid FROM pg_index WHERE indexrelid::regclass::text = 'book_date_idx';
indisvalid ------------ t (1 row)
连接和断开部分
没有提供节的自动创建。 因此，必须先创建它们，然后再将具有分区键新值的记录添加到表中。
当其他事务正在处理该表时，我们将创建一个新部分，同时我们将查看锁：
=> BEGIN; => SELECT count(*) FROM bookings_range WHERE book_date = to_timestamp('01.07.2017','DD.MM.YYYY');
count ------- 5 (1 row)
=> SELECT relation::regclass::text, mode FROM pg_locks WHERE pid = pg_backend_pid() AND relation::regclass::text LIKE 'bookings%';
relation | mode -----------------------+----------------- bookings_range_201708 | AccessShareLock bookings_range_201707 | AccessShareLock bookings_range_201706 | AccessShareLock bookings_range | AccessShareLock (4 rows)
AccessShareLock锁强加于主表，语句开头的所有节和索引。 to_timestamp函数的计算和节的排除在以后进行。 如果使用常量而不是函数，则只会锁定主表和bookings_range_201707部分。 因此，如果可能，请在请求中指定常量-应该这样做，否则pg_locks中的行数将与段数成比例地增加，这可能导致需要增加max_locks_per_transaction。
在不完成之前的交易的情况下，在新的会话中为9月创建以下部分：
|| => CREATE TABLE bookings_range_201709 (LIKE bookings_range); || => BEGIN; || => ALTER TABLE bookings_range ATTACH PARTITION bookings_range_201709 FOR VALUES FROM ('2017-09-01'::timestamptz) TO ('2017-10-01'::timestamptz); || => SELECT relation::regclass::text, mode FROM pg_locks WHERE pid = pg_backend_pid() AND relation::regclass::text LIKE 'bookings%';
relation | mode -------------------------------------+-------------------------- bookings_range_201709_book_date_idx | AccessExclusiveLock bookings_range | ShareUpdateExclusiveLock bookings_range_201709 | ShareLock bookings_range_201709 | AccessExclusiveLock (4 rows)
创建新节时，与AccessShareLock兼容的ShareUpdateExclusiveLock锁被强加到主表上。 因此，添加分区的操作不会与针对分区表的查询冲突。
=> COMMIT;
|| => COMMIT;
使用ALTER TABLE ... DETACH PARTITION命令完成分区。 该节本身不会被删除，而是成为一个独立的表。 可以从中下载数据，可以将其删除，并在必要时重新连接（ATTACH PARTITION）。
另一个禁用的选项是使用DROP TABLE命令删除该部分。
不幸的是，DROP TABLE和DETACH PARTITION这两个选项都在主表上使用AccessExclusiveLock锁。
默认部分
如果您尝试添加尚未为其创建节的记录，则会发生错误。 如果不需要此行为，则可以创建一个默认部分：
=> CREATE TABLE bookings_range_default PARTITION OF bookings_range DEFAULT;
假设添加记录时，它们混合了日期而未指定千年：
=> INSERT INTO bookings_range VALUES('XX0000', '0017-09-01'::timestamptz, 0) RETURNING tableoid::regclass, *;
tableoid | book_ref | book_date | total_amount ------------------------+----------+------------------------------+-------------- bookings_range_default | XX0000 | 0017-09-01 00:00:00+02:30:17 | 0.00 (1 row) INSERT 0 1
我们注意到短语RETURNING返回新行，该行属于默认部分。
设置当前日期（更改分区键）后，记录将自动移至所需部分，不需要触发器：
=> UPDATE bookings_range SET book_date = '2017-09-01'::timestamptz WHERE book_ref = 'XX0000' RETURNING tableoid::regclass, *;
tableoid | book_ref | book_date | total_amount -----------------------+----------+------------------------+-------------- bookings_range_201709 | XX0000 | 2017-09-01 00:00:00+03 | 0.00 (1 row) UPDATE 1
值列表分段
在演示数据库中，book_ref列必须是bookings表的主键。 但是，所选的分区方案不允许创建这样的密钥：
=> ALTER TABLE bookings_range ADD PRIMARY KEY(book_ref);
ERROR: insufficient columns in PRIMARY KEY constraint definition DETAIL: PRIMARY KEY constraint on table "bookings_range" lacks column "book_date" which is part of the partition key.
分区键必须包含在主键中。
要按月细分，并且仍将book_ref包含在主键中，让我们尝试另一种方案来对预订表进行分区-根据值列表。 为此，将多余的列book_month添加为分区键：
=> CREATE TABLE bookings_list ( book_ref character(6), book_month character(6), book_date timestamptz NOT NULL, total_amount numeric(10,2), PRIMARY KEY (book_ref, book_month) ) PARTITION BY LIST(book_month);
我们将基于预订表数据动态地形成部分：
=> WITH dates AS ( SELECT date_trunc('month',min(book_date)) min_date, date_trunc('month',max(book_date)) max_date FROM bookings ), partition AS ( SELECT to_char(g.month, 'YYYYMM') AS book_month FROM dates, generate_series(dates.min_date, dates.max_date, '1 month'::interval) AS g(month) ) SELECT format('CREATE TABLE %I PARTITION OF bookings_list FOR VALUES IN (%L)', 'bookings_list_' || partition.book_month, partition.book_month) FROM partition\gexec
CREATE TABLE CREATE TABLE CREATE TABLE
这是发生了什么：
=> \d+ bookings_list
Partitioned table "bookings.bookings_list" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description --------------+--------------------------+-----------+----------+---------+----------+--------------+------------- book_ref | character(6) | | not null | | extended | | book_month | character(6) | | not null | | extended | | book_date | timestamp with time zone | | not null | | plain | | total_amount | numeric(10,2) | | | | main | | Partition key: LIST (book_month) Indexes: "bookings_list_pkey" PRIMARY KEY, btree (book_ref, book_month) Partitions: bookings_list_201706 FOR VALUES IN ('201706'), bookings_list_201707 FOR VALUES IN ('201707'), bookings_list_201708 FOR VALUES IN ('201708')
在各部分中填充布局：
=> INSERT INTO bookings_list(book_ref,book_month,book_date,total_amount) SELECT book_ref,to_char(book_date, 'YYYYMM'),book_date,total_amount FROM bookings;
INSERT 0 262788
作为撤退。 要自动填充book_month，很容易使用版本12的新功能-GENERATED ALWAYS列。 但是，不幸的是，它们不能用作分区键。 因此，应以其他方式解决填充月份的任务。
可以在分区表上创建完整性约束，例如CHECK和NOT NULL。 与继承一样，指定INHERIT / NOINHERIT表示是否应在所有分区表上继承限制。 默认继承：
=> ALTER TABLE bookings_range ALTER COLUMN book_date SET NOT NULL;
=> \d bookings_range
Partitioned table "bookings.bookings_range" Column | Type | Collation | Nullable | Default --------------+--------------------------+-----------+----------+--------- book_ref | character(6) | | | book_date | timestamp with time zone | | not null | total_amount | numeric(10,2) | | | Partition key: RANGE (book_date) Indexes: "book_date_idx" btree (book_date) Number of partitions: 5 (Use \d+ to list them.)
=> \d bookings_range_201706
Table "bookings.bookings_range_201706" Column | Type | Collation | Nullable | Default --------------+--------------------------+-----------+----------+--------- book_ref | character(6) | | | book_date | timestamp with time zone | | not null | total_amount | numeric(10,2) | | | Partition of: bookings_range FOR VALUES FROM ('2017-06-01 00:00:00+03') TO ('2017-07-01 00:00:00+03') Indexes: "book_date_201706_idx" btree (book_date)
EXCLUDE约束只能在分区上本地创建。
由于book_ref列在第一位，因此对book_ref的搜索将遍历所有部分，但按索引进行查找：
=> EXPLAIN (COSTS OFF) SELECT * FROM bookings_list WHERE book_ref = '00000F';
QUERY PLAN -------------------------------------------------------------------------- Append -> Index Scan using bookings_list_201706_pkey on bookings_list_201706 Index Cond: (book_ref = '00000F'::bpchar) -> Index Scan using bookings_list_201707_pkey on bookings_list_201707 Index Cond: (book_ref = '00000F'::bpchar) -> Index Scan using bookings_list_201708_pkey on bookings_list_201708 Index Cond: (book_ref = '00000F'::bpchar) (7 rows)
在book_ref和一系列区域上的搜索应仅在指定范围内进行：
=> EXPLAIN (COSTS OFF) SELECT * FROM bookings_list WHERE book_ref = '00000F' AND book_month = '201707';
QUERY PLAN ----------------------------------------------------------------------------------- Index Scan using bookings_list_201707_pkey on bookings_list_201707 Index Cond: ((book_ref = '00000F'::bpchar) AND (book_month = '201707'::bpchar)) (2 rows)
INSERT ... ON CONFLICT命令正确找到所需的部分并执行更新：
=> INSERT INTO bookings_list VALUES ('XX0001','201708','2017-08-01',0) RETURNING tableoid::regclass, *;
tableoid | book_ref | book_month | book_date | total_amount ----------------------+----------+------------+------------------------+-------------- bookings_list_201708 | XX0001 | 201708 | 2017-08-01 00:00:00+03 | 0.00 (1 row) INSERT 0 1
=> INSERT INTO bookings_list VALUES ('XX0001','201708','2017-08-01',100) ON CONFLICT(book_ref,book_month) DO UPDATE SET total_amount = 100 RETURNING tableoid::regclass, *;
tableoid | book_ref | book_month | book_date | total_amount ----------------------+----------+------------+------------------------+-------------- bookings_list_201708 | XX0001 | 201708 | 2017-08-01 00:00:00+03 | 100.00 (1 row) INSERT 0 1
外键
在演示数据库中，票证表指的是预订。
要使外键成为可能，请添加book_month列，并同时按月将其分成几部分，例如bookings_list。
=> CREATE TABLE tickets_list ( ticket_no character(13), book_month character(6), book_ref character(6) NOT NULL, passenger_id varchar(20) NOT NULL, passenger_name text NOT NULL, contact_data jsonb, PRIMARY KEY (ticket_no, book_month), FOREIGN KEY (book_ref, book_month) REFERENCES bookings_list (book_ref, book_month) ) PARTITION BY LIST (book_month);
值得一看的外键限制。 一方面，这是分区表（tickets_list）中的外键
，另一方面，这是分区表（bookings_list）中的键。 因此，双向支持分区表的外键。
创建部分：
=> WITH dates AS ( SELECT date_trunc('month',min(book_date)) min_date, date_trunc('month',max(book_date)) max_date FROM bookings ), partition AS ( SELECT to_char(g.month, 'YYYYMM') AS book_month FROM dates, generate_series(dates.min_date, dates.max_date, '1 month'::interval) AS g(month) ) SELECT format('CREATE TABLE %I PARTITION OF tickets_list FOR VALUES IN (%L)', 'tickets_list_' || partition.book_month, partition.book_month) FROM partition\gexec
CREATE TABLE CREATE TABLE CREATE TABLE
=> \d+ tickets_list
Partitioned table "bookings.tickets_list" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ----------------+-----------------------+-----------+----------+---------+----------+--------------+------------- ticket_no | character(13) | | not null | | extended | | book_month | character(6) | | not null | | extended | | book_ref | character(6) | | not null | | extended | | passenger_id | character varying(20) | | not null | | extended | | passenger_name | text | | not null | | extended | | contact_data | jsonb | | | | extended | | Partition key: LIST (book_month) Indexes: "tickets_list_pkey" PRIMARY KEY, btree (ticket_no, book_month) Foreign-key constraints: "tickets_list_book_ref_book_month_fkey" FOREIGN KEY (book_ref, book_month) REFERENCES bookings_list(book_ref, book_month) Partitions: tickets_list_201706 FOR VALUES IN ('201706'), tickets_list_201707 FOR VALUES IN ('201707'), tickets_list_201708 FOR VALUES IN ('201708')
我们填写：
=> INSERT INTO tickets_list (ticket_no,book_month,book_ref,passenger_id,passenger_name,contact_data) SELECT t.ticket_no,b.book_month,t.book_ref, t.passenger_id,t.passenger_name,t.contact_data FROM bookings_list b JOIN tickets t ON (b.book_ref = t.book_ref);
INSERT 0 366733
=> VACUUM ANALYZE tickets_list;
线段分布：
=> SELECT tableoid::regclass, count(*) FROM tickets_list GROUP BY tableoid;
tableoid | count ---------------------+-------- tickets_list_201706 | 10160 tickets_list_201707 | 232755 tickets_list_201708 | 123818 (3 rows)
连接和聚合请求
以相同的方式对两个表进行分区：
=> EXPLAIN (COSTS OFF) SELECT b.* FROM bookings_list b JOIN tickets_list t ON (b.book_ref = t.book_ref and b.book_month = t.book_month);
QUERY PLAN ---------------------------------------------------------------------------- Hash Join Hash Cond: ((t.book_ref = b.book_ref) AND (t.book_month = b.book_month)) -> Append -> Seq Scan on tickets_list_201706 t -> Seq Scan on tickets_list_201707 t_1 -> Seq Scan on tickets_list_201708 t_2 -> Hash -> Append -> Seq Scan on bookings_list_201706 b -> Seq Scan on bookings_list_201707 b_1 -> Seq Scan on bookings_list_201708 b_2 (11 rows)
在开始连接之前，每个表首先合并属于查询条件的部分。
但是您可以先合并两个表的相应月度部分，然后合并结果。 这可以通过启用enable_partitionwise_join参数来实现：
=> SET enable_partitionwise_join = ON; => EXPLAIN (COSTS OFF) SELECT b.* FROM bookings_list b JOIN tickets_list t ON (b.book_ref = t.book_ref and b.book_month = t.book_month);
QUERY PLAN ------------------------------------------------------------------------------------------ Append -> Hash Join Hash Cond: ((t.book_ref = b.book_ref) AND (t.book_month = b.book_month)) -> Seq Scan on tickets_list_201706 t -> Hash -> Seq Scan on bookings_list_201706 b -> Hash Join Hash Cond: ((t_1.book_ref = b_1.book_ref) AND (t_1.book_month = b_1.book_month)) -> Seq Scan on tickets_list_201707 t_1 -> Hash -> Seq Scan on bookings_list_201707 b_1 -> Hash Join Hash Cond: ((t_2.book_ref = b_2.book_ref) AND (t_2.book_month = b_2.book_month)) -> Seq Scan on tickets_list_201708 t_2 -> Hash -> Seq Scan on bookings_list_201708 b_2 (16 rows)
现在，首先将两个表的相应部分进行联接，然后将联接的结果合并。
与聚合类似的情况：
=> EXPLAIN (COSTS OFF) SELECT count(*) FROM bookings_list;
QUERY PLAN ------------------------------------------------------------------- Finalize Aggregate -> Gather Workers Planned: 2 -> Partial Aggregate -> Parallel Append -> Parallel Seq Scan on bookings_list_201707 -> Parallel Seq Scan on bookings_list_201708 -> Parallel Seq Scan on bookings_list_201706 (8 rows)
请注意，部分扫描可以并行执行。 但是首先将各个部分放在一起，然后才开始聚合。 另外，您可以在每个部分中进行汇总，然后合并结果：
=> SET enable_partitionwise_aggregate = ON; => EXPLAIN (COSTS OFF) SELECT count(*) FROM bookings_list;
QUERY PLAN ------------------------------------------------------------------- Finalize Aggregate -> Gather Workers Planned: 2 -> Parallel Append -> Partial Aggregate -> Parallel Seq Scan on bookings_list_201707 -> Partial Aggregate -> Parallel Seq Scan on bookings_list_201708 -> Partial Aggregate -> Parallel Seq Scan on bookings_list_201706 (10 rows)
如果部分的一部分是外部表，则这些功能特别重要。 默认情况下，两者都被禁用，因为 适当的参数会影响计划的时间，但不一定总是使用。
哈希分区
分区表的第三种方法是哈希分区。
创建表：
=> CREATE TABLE bookings_hash ( book_ref character(6) PRIMARY KEY, book_date timestamptz NOT NULL, total_amount numeric(10,2) ) PARTITION BY HASH(book_ref);
在此版本的book_ref中，您可以立即将其声明为主键作为分区键。
分为三个部分：
=> CREATE TABLE bookings_hash_p0 PARTITION OF bookings_hash FOR VALUES WITH (MODULUS 3, REMAINDER 0); => CREATE TABLE bookings_hash_p1 PARTITION OF bookings_hash FOR VALUES WITH (MODULUS 3, REMAINDER 1); => CREATE TABLE bookings_hash_p2 PARTITION OF bookings_hash FOR VALUES WITH (MODULUS 3, REMAINDER 2);
在各部分中填充自动布局：
=> INSERT INTO bookings_hash SELECT * FROM bookings;
INSERT 0 262788
各部分中的线分布均匀：
=> SELECT tableoid::regclass AS partition, count(*) FROM bookings_hash GROUP BY tableoid;
partition | count ------------------+------- bookings_hash_p0 | 87649 bookings_hash_p1 | 87651 bookings_hash_p2 | 87488 (3 rows)
查看分区对象的新命令：
=> \dP+
List of partitioned relations Schema | Name | Owner | Type | Table | Total size | Description ----------+--------------------+---------+-------------------+----------------+------------+------------- bookings | bookings_hash | student | partitioned table | | 13 MB | bookings | bookings_list | student | partitioned table | | 15 MB | bookings | bookings_range | student | partitioned table | | 13 MB | bookings | tickets_list | student | partitioned table | | 50 MB | bookings | book_date_idx | student | partitioned index | bookings_range | 5872 kB | bookings | bookings_hash_pkey | student | partitioned index | bookings_hash | 5800 kB | bookings | bookings_list_pkey | student | partitioned index | bookings_list | 8120 kB | bookings | tickets_list_pkey | student | partitioned index | tickets_list | 19 MB | (8 rows)
=> VACUUM ANALYZE bookings_hash;
子查询和嵌套循环联接
使用嵌套循环连接可以在运行时排除节。
前10个部分的预订分布：
=> WITH top10 AS ( SELECT tableoid::regclass AS partition, * FROM bookings_hash ORDER BY book_ref LIMIT 10 ) SELECT partition, count(*) FROM top10 GROUP BY 1 ORDER BY 1;
partition | count ------------------+------- bookings_hash_p0 | 3 bookings_hash_p1 | 3 bookings_hash_p2 | 4 (3 rows)
让我们看一下带有bookings_hash表和上一个子查询的联接的查询执行计划： => EXPLAIN (ANALYZE,COSTS OFF,TIMING OFF) WITH top10 AS ( SELECT tableoid::regclass AS partition, * FROM bookings ORDER BY book_ref LIMIT 10 ) SELECT bh.* FROM bookings_hash bh JOIN top10 ON bh.book_ref = top10.book_ref;
QUERY PLAN ----------------------------------------------------------------------------------------------------- Nested Loop (actual rows=10 loops=1) -> Limit (actual rows=10 loops=1) -> Index Only Scan using bookings_pkey on bookings (actual rows=10 loops=1) Heap Fetches: 0 -> Append (actual rows=1 loops=10) -> Index Scan using bookings_hash_p0_pkey on bookings_hash_p0 bh (actual rows=1 loops=3) Index Cond: (book_ref = bookings.book_ref) -> Index Scan using bookings_hash_p1_pkey on bookings_hash_p1 bh_1 (actual rows=1 loops=3) Index Cond: (book_ref = bookings.book_ref) -> Index Scan using bookings_hash_p2_pkey on bookings_hash_p2 bh_2 (actual rows=1 loops=4) Index Cond: (book_ref = bookings.book_ref) Planning Time: 0.632 ms Execution Time: 0.278 ms (13 rows)
使用嵌套循环方法进行连接。根据通用表表达式的外循环执行10次。但是要注意对表节（循环）的调用次数。对于外循环的每个book_ref值，仅扫描该值存储在bookings_hash表中的部分。与禁用部分排除进行比较： => SET enable_partition_pruning TO OFF; => EXPLAIN (ANALYZE,COSTS OFF,TIMING OFF) WITH top10 AS ( SELECT tableoid::regclass AS partition, * FROM bookings ORDER BY book_ref LIMIT 10 ) SELECT bh.* FROM bookings_hash bh JOIN top10 ON bh.book_ref = top10.book_ref;
QUERY PLAN ------------------------------------------------------------------------------------------------------ Nested Loop (actual rows=10 loops=1) -> Limit (actual rows=10 loops=1) -> Index Only Scan using bookings_pkey on bookings (actual rows=10 loops=1) Heap Fetches: 0 -> Append (actual rows=1 loops=10) -> Index Scan using bookings_hash_p0_pkey on bookings_hash_p0 bh (actual rows=0 loops=10) Index Cond: (book_ref = bookings.book_ref) -> Index Scan using bookings_hash_p1_pkey on bookings_hash_p1 bh_1 (actual rows=0 loops=10) Index Cond: (book_ref = bookings.book_ref) -> Index Scan using bookings_hash_p2_pkey on bookings_hash_p2 bh_2 (actual rows=0 loops=10) Index Cond: (book_ref = bookings.book_ref) Planning Time: 0.886 ms Execution Time: 0.771 ms (13 rows)
=> RESET enable_partition_pruning;
如果您将选择减少到一个保留，那么两个部分将完全不可见： => EXPLAIN (ANALYZE,COSTS OFF,TIMING OFF) WITH top AS ( SELECT tableoid::regclass AS partition, * FROM bookings ORDER BY book_ref LIMIT 1 ) SELECT bh.* FROM bookings_hash bh JOIN top ON bh.book_ref = top.book_ref;
QUERY PLAN --------------------------------------------------------------------------------------------------- Nested Loop (actual rows=1 loops=1) -> Limit (actual rows=1 loops=1) -> Index Only Scan using bookings_pkey on bookings (actual rows=1 loops=1) Heap Fetches: 0 -> Append (actual rows=1 loops=1) -> Index Scan using bookings_hash_p0_pkey on bookings_hash_p0 bh (actual rows=1 loops=1) Index Cond: (book_ref = bookings.book_ref) -> Index Scan using bookings_hash_p1_pkey on bookings_hash_p1 bh_1 (never executed) Index Cond: (book_ref = bookings.book_ref) -> Index Scan using bookings_hash_p2_pkey on bookings_hash_p2 bh_2 (never executed) Index Cond: (book_ref = bookings.book_ref) Planning Time: 0.250 ms Execution Time: 0.090 ms (13 rows)
代替子查询，您可以使用该函数返回具有可变性STABLE类别的集合： => CREATE OR REPLACE FUNCTION get_book_ref(top int) RETURNS SETOF bookings AS $$ BEGIN RETURN QUERY EXECUTE 'SELECT * FROM bookings ORDER BY book_ref LIMIT $1' USING top; END;$$ LANGUAGE plpgsql STABLE;
=> EXPLAIN (ANALYZE,COSTS OFF,TIMING OFF) SELECT * FROM bookings_hash bh JOIN get_book_ref(10) f ON bh.book_ref = f.book_ref;
QUERY PLAN ----------------------------------------------------------------------------------------------------- Nested Loop (actual rows=10 loops=1) -> Function Scan on get_book_ref f (actual rows=10 loops=1) -> Append (actual rows=1 loops=10) -> Index Scan using bookings_hash_p0_pkey on bookings_hash_p0 bh (actual rows=1 loops=3) Index Cond: (book_ref = f.book_ref) -> Index Scan using bookings_hash_p1_pkey on bookings_hash_p1 bh_1 (actual rows=1 loops=3) Index Cond: (book_ref = f.book_ref) -> Index Scan using bookings_hash_p2_pkey on bookings_hash_p2 bh_2 (actual rows=1 loops=4) Index Cond: (book_ref = f.book_ref) Planning Time: 0.175 ms Execution Time: 0.843 ms (11 rows)
总结
综上所述，我们可以说PostgreSQL 12中的内置或声明性分区具有丰富的功能，可以安全地建议通过继承替换分区。