Skip to content

Commit 8eae787

Browse files
zilderzilder
committed
documentation fixed
1 parent c60d242 commit 8eae787

File tree

3 files changed

+86
-8
lines changed

3 files changed

+86
-8
lines changed

‎README.md

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

‎README.rus.md

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

‎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)