Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit8eae787

Browse files
committed
documentation fixed
1 parentc60d242 commit8eae787

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 usedisable_pathman() function:
203+
To disable pg_pathman for some previously partitioned table usedisable_partitioning() function:
166204
```
167-
SELECTdisable_pathman('range_rel');
205+
SELECTdisable_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-
SELECTdisable_pathman('range_rel');
209+
SELECTdisable_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
DELETEFROM @extschema@.pathman_configWHERE relname= relation;
81-
EXECUTE format('DROPTRIGGER %s_insert_trigger_func ON %1$s', relation);
81+
EXECUTE format('DROPFUNCTION IF EXISTS %s_insert_trigger_func() CASCADE', relation);
8282

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

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp