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

Commit6f14084

Browse files
committed
Merge branch 'pathman_pgpro9_5' into PGPRO9_5
2 parentsc8ed341 +2c109ac commit6f14084

File tree

5 files changed

+250
-47
lines changed

5 files changed

+250
-47
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 usedisable_pathman() function:
200+
To disable pg_pathman for some previously partitioned table usedisable_partitioning() function:
163201
```
164-
SELECTdisable_pathman('range_rel');
202+
SELECTdisable_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-
SELECTdisable_pathman('range_rel');
207+
SELECTdisable_partitioning('range_rel');
168208
```
169209
Все созданные секции и данные останутся по прежнему доступны и будут обрабатываться стандартным планировщиком PostgreSQL.
170210
###Ручное управление секциями

‎contrib/pg_pathman/pg_pathman.c‎

Lines changed: 6 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -10,6 +10,7 @@
1010
#include"optimizer/pathnode.h"
1111
#include"optimizer/planner.h"
1212
#include"optimizer/restrictinfo.h"
13+
#include"optimizer/cost.h"
1314
#include"utils/hsearch.h"
1415
#include"utils/tqual.h"
1516
#include"utils/rel.h"
@@ -105,7 +106,7 @@ _PG_init(void)
105106
{
106107
elog(ERROR,"Pathman module must be initialized in postmaster. "
107108
"Put the following line to configuration file: "
108-
"shared_preload_library ='pg_pathman'");
109+
"shared_preload_libraries='pg_pathman'");
109110
initialization_needed= false;
110111
}
111112

@@ -645,8 +646,10 @@ handle_binary_opexpr(const PartRelationInfo *prel, WrapperNode *result,
645646
RangeEntry*re;
646647
boolfound= false,
647648
lossy= false;
648-
intcounter=0,
649-
startidx=0,
649+
#ifdefUSE_ASSERT_CHECKING
650+
intcounter=0;
651+
#endif
652+
intstartidx=0,
650653
cmp_min,
651654
cmp_max,
652655
endidx=rangerel->ranges.length-1;

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