Skip to content

Commit 98b4620

Browse files
zilderzilder
committed
pathman: documentation
1 parent 801a2e8 commit 98b4620

File tree

3 files changed

+86
-8
lines changed

3 files changed

+86
-8
lines changed

‎contrib/pg_pathman/README.md

Lines changed: 41 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -129,6 +129,27 @@ This will create new partitions but data will still be in the parent table. To m
129129
```
130130
SELECT partition_data('hash_rel');
131131
```
132+
Here is an example of the query with filtering by partitioning key and its plan:
133+
```
134+
SELECT * FROM hash_rel WHERE value = 1234;
135+
id | value
136+
------+-------
137+
1234 | 1234
138+
139+
EXPLAIN SELECT * FROM hash_rel WHERE value = 1234;
140+
QUERY PLAN
141+
-----------------------------------------------------------------
142+
Append (cost=0.00..2.00 rows=0 width=0)
143+
-> Seq Scan on hash_rel_34 (cost=0.00..2.00 rows=0 width=0)
144+
Filter: (value = 1234)
145+
```
146+
Note that pg_pathman exludes parent table from the query plan. To access parent table use ONLY modifier:
147+
```
148+
EXPLAIN SELECT * FROM ONLY hash_rel;
149+
QUERY PLAN
150+
--------------------------------------------------------
151+
Seq Scan on hash_rel (cost=0.00..0.00 rows=1 width=8)
152+
```
132153
### RANGE
133154
Consider an example of RANGE partitioning. Create a table with numerical or date or timestamp column:
134155
```
@@ -156,12 +177,29 @@ SELECT split_range_partition('range_rel_1', '2010-02-15'::date);
156177
Now let's create new partition. You can use append_partition() or prepend_partition() functions:
157178
```
158179
SELECT append_partition('range_rel');
159-
SELECT prepend_partition('range_rel');
160180
```
181+
Here is an example of the query with filtering by partitioning key and its plan:
182+
```
183+
SELECT * FROM range_rel WHERE dt >= '2012-04-30' AND dt <= '2012-05-01';
184+
id | dt
185+
-----+---------------------
186+
851 | 2012-04-30 00:00:00
187+
852 | 2012-05-01 00:00:00
188+
189+
EXPLAIN SELECT * FROM range_rel WHERE dt >= '2012-04-30' AND dt <= '2012-05-01';
190+
QUERY PLAN
191+
----------------------------------------------------------------------------
192+
Append (cost=0.00..60.80 rows=0 width=0)
193+
-> Seq Scan on range_rel_28 (cost=0.00..30.40 rows=0 width=0)
194+
Filter: (dt >= '2012-04-30 00:00:00'::timestamp without time zone)
195+
-> Seq Scan on range_rel_29 (cost=0.00..30.40 rows=0 width=0)
196+
Filter: (dt <= '2012-05-01 00:00:00'::timestamp without time zone)
197+
```
198+
161199
### Disable pg_pathman
162-
To disable pg_pathman for some previously partitioned table use disable_pathman() function:
200+
To disable pg_pathman for some previously partitioned table use disable_partitioning() function:
163201
```
164-
SELECT disable_pathman('range_rel');
202+
SELECT disable_partitioning('range_rel');
165203
```
166204
All sections and data will stay available and will be handled by standard PostgreSQL partitioning mechanism.
167205
### Manual partitions management

‎contrib/pg_pathman/README.rus.md

Lines changed: 43 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -131,6 +131,28 @@ SELECT create_hash_partitions('hash_rel', 'value', 100);
131131
```
132132
SELECT partition_data('hash_rel');
133133
```
134+
Пример построения плана для запроса с фильтрацией по ключевому полю:
135+
```
136+
SELECT * FROM hash_rel WHERE value = 1234;
137+
id | value
138+
------+-------
139+
1234 | 1234
140+
141+
EXPLAIN SELECT * FROM hash_rel WHERE value = 1234;
142+
QUERY PLAN
143+
-----------------------------------------------------------------
144+
Append (cost=0.00..2.00 rows=0 width=0)
145+
-> Seq Scan on hash_rel_34 (cost=0.00..2.00 rows=0 width=0)
146+
Filter: (value = 1234)
147+
```
148+
Стоит отметить, что pg_pathman исключает из плана запроса родительскую таблицу, и чтобы получить данные из нее, следует использовать модификатор ONLY:
149+
```
150+
EXPLAIN SELECT * FROM ONLY hash_rel;
151+
QUERY PLAN
152+
--------------------------------------------------------
153+
Seq Scan on hash_rel (cost=0.00..0.00 rows=1 width=8)
154+
```
155+
134156
### RANGE
135157
Пример секционирования таблицы с использованием стратегии RANGE.
136158
```
@@ -159,12 +181,30 @@ SELECT split_range_partition('range_rel_1', '2010-02-15'::date);
159181
```
160182
Добавим новую секцию в конец списка секций:
161183
```
162-
SELECT append_partition('range_rel')
184+
SELECT append_partition('range_rel');
185+
```
186+
Пример построения плана для запроса с фильтрацией по ключевому полю:
163187
```
188+
SELECT * FROM range_rel WHERE dt >= '2012-04-30' AND dt <= '2012-05-01';
189+
id | dt
190+
-----+---------------------
191+
851 | 2012-04-30 00:00:00
192+
852 | 2012-05-01 00:00:00
193+
194+
EXPLAIN SELECT * FROM range_rel WHERE dt >= '2012-04-30' AND dt <= '2012-05-01';
195+
QUERY PLAN
196+
----------------------------------------------------------------------------
197+
Append (cost=0.00..60.80 rows=0 width=0)
198+
-> Seq Scan on range_rel_28 (cost=0.00..30.40 rows=0 width=0)
199+
Filter: (dt >= '2012-04-30 00:00:00'::timestamp without time zone)
200+
-> Seq Scan on range_rel_29 (cost=0.00..30.40 rows=0 width=0)
201+
Filter: (dt <= '2012-05-01 00:00:00'::timestamp without time zone)
202+
```
203+
164204
### Деакцивация pathman
165-
Деактивировать pathman для некоторой ранее разделенной таблицы можно следующей командой disable_pathman():
205+
Деактивировать pathman для некоторой ранее разделенной таблицы можно следующей командой disable_partitioning():
166206
```
167-
SELECT disable_pathman('range_rel');
207+
SELECT disable_partitioning('range_rel');
168208
```
169209
Все созданные секции и данные останутся по прежнему доступны и будут обрабатываться стандартным планировщиком PostgreSQL.
170210
### Ручное управление секциями

‎contrib/pg_pathman/sql/init.sql

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -78,10 +78,10 @@ RETURNS VOID AS
7878
$$
7979
BEGIN
8080
DELETE FROM @extschema@.pathman_config WHERE relname = relation;
81-
EXECUTE format('DROP TRIGGER %s_insert_trigger_func ON %1$s', relation);
81+
EXECUTE format('DROP FUNCTION IF EXISTS %s_insert_trigger_func() CASCADE', relation);
8282

8383
/* Notify backend about changes */
84-
PERFORM pg_pathman_on_remove_partitions(relation::regclass::integer);
84+
PERFORM on_remove_partitions(relation::regclass::integer);
8585
END
8686
$$
8787
LANGUAGE plpgsql;

0 commit comments

Comments
 (0)