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

Commit2f9dbb2

Browse files
committed
Merge branch 'pathman_pgpro9_5' of gitlab.postgrespro.ru:pgpro-dev/postgrespro into pathman_pgpro9_5
2 parents0de7bbb +b04aa13 commit2f9dbb2

File tree

8 files changed

+265
-60
lines changed

8 files changed

+265
-60
lines changed

‎contrib/pg_pathman/README.md‎

Lines changed: 43 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -37,8 +37,7 @@ Based on partitioning type and operator the `pg_pathman` searches corresponding
3737

3838
To install pg_pathman run in psql:
3939
```
40-
CREATE SCHEMA pathman;
41-
CREATE EXTENSION pg_pathman SCHEMA pathman;
40+
CREATE EXTENSION pg_pathman;
4241
```
4342
Then modify shared_preload_libraries parameter in postgres.conf as following:
4443
```
@@ -121,14 +120,35 @@ CREATE TABLE hash_rel (
121120
value INTEGER);
122121
INSERT INTO hash_rel (value) SELECT g FROM generate_series(1, 10000) as g;
123122
```
124-
Then run create_hash_partitions() function with appropriate arguments:
123+
If partitions are supposed to have indexes, then they should be created for parent table before partitioning. In this case pg_pathman will automaticaly create indexes for partitions.Then run create_hash_partitions() function with appropriate arguments:
125124
```
126125
SELECT create_hash_partitions('hash_rel', 'value', 100);
127126
```
128127
This will create new partitions but data will still be in the parent table. To move data to the corresponding partitions use partition_data() function:
129128
```
130129
SELECT partition_data('hash_rel');
131130
```
131+
Here is an example of the query with filtering by partitioning key and its plan:
132+
```
133+
SELECT * FROM hash_rel WHERE value = 1234;
134+
id | value
135+
------+-------
136+
1234 | 1234
137+
138+
EXPLAIN SELECT * FROM hash_rel WHERE value = 1234;
139+
QUERY PLAN
140+
-----------------------------------------------------------------
141+
Append (cost=0.00..2.00 rows=0 width=0)
142+
-> Seq Scan on hash_rel_34 (cost=0.00..2.00 rows=0 width=0)
143+
Filter: (value = 1234)
144+
```
145+
Note that pg_pathman exludes parent table from the query plan. To access parent table use ONLY modifier:
146+
```
147+
EXPLAIN SELECT * FROM ONLY hash_rel;
148+
QUERY PLAN
149+
--------------------------------------------------------
150+
Seq Scan on hash_rel (cost=0.00..0.00 rows=1 width=8)
151+
```
132152
###RANGE
133153
Consider an example of RANGE partitioning. Create a table with numerical or date or timestamp column:
134154
```
@@ -156,12 +176,29 @@ SELECT split_range_partition('range_rel_1', '2010-02-15'::date);
156176
Now let's create new partition. You can use append_partition() or prepend_partition() functions:
157177
```
158178
SELECT append_partition('range_rel');
159-
SELECT prepend_partition('range_rel');
160179
```
180+
Here is an example of the query with filtering by partitioning key and its plan:
181+
```
182+
SELECT * FROM range_rel WHERE dt >= '2012-04-30' AND dt <= '2012-05-01';
183+
id | dt
184+
-----+---------------------
185+
851 | 2012-04-30 00:00:00
186+
852 | 2012-05-01 00:00:00
187+
188+
EXPLAIN SELECT * FROM range_rel WHERE dt >= '2012-04-30' AND dt <= '2012-05-01';
189+
QUERY PLAN
190+
----------------------------------------------------------------------------
191+
Append (cost=0.00..60.80 rows=0 width=0)
192+
-> Seq Scan on range_rel_28 (cost=0.00..30.40 rows=0 width=0)
193+
Filter: (dt >= '2012-04-30 00:00:00'::timestamp without time zone)
194+
-> Seq Scan on range_rel_29 (cost=0.00..30.40 rows=0 width=0)
195+
Filter: (dt <= '2012-05-01 00:00:00'::timestamp without time zone)
196+
```
197+
161198
###Disable pg_pathman
162-
To disable pg_pathman for some previously partitioned table usedisable_pathman() function:
199+
To disable pg_pathman for some previously partitioned table usedisable_partitioning() function:
163200
```
164-
SELECTdisable_pathman('range_rel');
201+
SELECTdisable_partitioning('range_rel');
165202
```
166203
All sections and data will stay available and will be handled by standard PostgreSQL partitioning mechanism.
167204
###Manual partitions management

‎contrib/pg_pathman/README.rus.md‎

Lines changed: 45 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -38,8 +38,7 @@ WHERE id = 150
3838

3939
Для установки pg_pathman выполните в командной строке:
4040
```
41-
CREATE SCHEMA pathman;
42-
CREATE EXTENSION pg_pathman SCHEMA pathman;
41+
CREATE EXTENSION pg_pathman;
4342
4443
```
4544
Затем модифицируйте параметр shared_preload_libraries в конфигурационном файле postgres.conf:
@@ -123,14 +122,36 @@ CREATE TABLE hash_rel (
123122
value INTEGER);
124123
INSERT INTO hash_rel (value) SELECT g FROM generate_series(1, 10000) as g;
125124
```
126-
Разобьем таблицу`hash_rel` на 100 секций по полю`value`:
125+
Если дочерние секции подразумевают наличие индексов, то стоит их создать в родительской таблице до разбиения. Тогда при разбиении pg_pathman автоматически создаст соответствующие индексы в дочерних.таблицах.Разобьем таблицу`hash_rel` на 100 секций по полю`value`:
127126
```
128127
SELECT create_hash_partitions('hash_rel', 'value', 100);
129128
```
130129
Перенесем данные из родительской таблицы в дочерние секции.
131130
```
132131
SELECT partition_data('hash_rel');
133132
```
133+
Пример построения плана для запроса с фильтрацией по ключевому полю:
134+
```
135+
SELECT * FROM hash_rel WHERE value = 1234;
136+
id | value
137+
------+-------
138+
1234 | 1234
139+
140+
EXPLAIN SELECT * FROM hash_rel WHERE value = 1234;
141+
QUERY PLAN
142+
-----------------------------------------------------------------
143+
Append (cost=0.00..2.00 rows=0 width=0)
144+
-> Seq Scan on hash_rel_34 (cost=0.00..2.00 rows=0 width=0)
145+
Filter: (value = 1234)
146+
```
147+
Стоит отметить, что pg_pathman исключает из плана запроса родительскую таблицу, и чтобы получить данные из нее, следует использовать модификатор ONLY:
148+
```
149+
EXPLAIN SELECT * FROM ONLY hash_rel;
150+
QUERY PLAN
151+
--------------------------------------------------------
152+
Seq Scan on hash_rel (cost=0.00..0.00 rows=1 width=8)
153+
```
154+
134155
###RANGE
135156
Пример секционирования таблицы с использованием стратегии RANGE.
136157
```
@@ -159,12 +180,30 @@ SELECT split_range_partition('range_rel_1', '2010-02-15'::date);
159180
```
160181
Добавим новую секцию в конец списка секций:
161182
```
162-
SELECT append_partition('range_rel')
183+
SELECT append_partition('range_rel');
184+
```
185+
Пример построения плана для запроса с фильтрацией по ключевому полю:
163186
```
187+
SELECT * FROM range_rel WHERE dt >= '2012-04-30' AND dt <= '2012-05-01';
188+
id | dt
189+
-----+---------------------
190+
851 | 2012-04-30 00:00:00
191+
852 | 2012-05-01 00:00:00
192+
193+
EXPLAIN SELECT * FROM range_rel WHERE dt >= '2012-04-30' AND dt <= '2012-05-01';
194+
QUERY PLAN
195+
----------------------------------------------------------------------------
196+
Append (cost=0.00..60.80 rows=0 width=0)
197+
-> Seq Scan on range_rel_28 (cost=0.00..30.40 rows=0 width=0)
198+
Filter: (dt >= '2012-04-30 00:00:00'::timestamp without time zone)
199+
-> Seq Scan on range_rel_29 (cost=0.00..30.40 rows=0 width=0)
200+
Filter: (dt <= '2012-05-01 00:00:00'::timestamp without time zone)
201+
```
202+
164203
###Деакцивация pathman
165-
Деактивировать pathman для некоторой ранее разделенной таблицы можно следующей командойdisable_pathman():
204+
Деактивировать pathman для некоторой ранее разделенной таблицы можно следующей командойdisable_partitioning():
166205
```
167-
SELECTdisable_pathman('range_rel');
206+
SELECTdisable_partitioning('range_rel');
168207
```
169208
Все созданные секции и данные останутся по прежнему доступны и будут обрабатываться стандартным планировщиком PostgreSQL.
170209
###Ручное управление секциями

‎contrib/pg_pathman/dsm_array.c‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -2,7 +2,7 @@
22
#include"storage/shmem.h"
33
#include"storage/dsm.h"
44
#include"storage/lwlock.h"
5-
5+
#include<stdint.h>
66

77
// static Table *table;
88
staticdsm_segment*segment=NULL;

‎contrib/pg_pathman/init.c‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -208,7 +208,7 @@ load_check_constraints(Oid parent_oid)
208208
{
209209
SPITupleTable*tuptable=SPI_tuptable;
210210
Oid*children;
211-
RangeEntry*ranges;
211+
RangeEntry*ranges=NULL;
212212
Datummin;
213213
Datummax;
214214
inthash;

‎contrib/pg_pathman/pg_pathman.c‎

Lines changed: 14 additions & 7 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,10 +646,12 @@ handle_binary_opexpr(const PartRelationInfo *prel, WrapperNode *result,
645646
if (rangerel!=NULL)
646647
{
647648
RangeEntry*re;
648-
boolfound= false,
649-
lossy= false;
650-
intcounter=0,
651-
startidx=0,
649+
boollossy= false;
650+
#ifdefUSE_ASSERT_CHECKING
651+
boolfound= false;
652+
intcounter=0;
653+
#endif
654+
intstartidx=0,
652655
cmp_min,
653656
cmp_max,
654657
endidx=rangerel->ranges.length-1;
@@ -717,7 +720,9 @@ handle_binary_opexpr(const PartRelationInfo *prel, WrapperNode *result,
717720
lossy= false;
718721
else
719722
lossy= true;
723+
#ifdefUSE_ASSERT_CHECKING
720724
found= true;
725+
#endif
721726
break;
722727
}
723728

@@ -807,8 +812,10 @@ range_binary_search(const RangeRelation *rangerel, FmgrInfo *cmp_func, Datum val
807812
cmp_max,
808813
i=0,
809814
startidx=0,
810-
endidx=rangerel->ranges.length-1,
811-
counter=0;
815+
endidx=rangerel->ranges.length-1;
816+
#ifdefUSE_ASSERT_CHECKING
817+
intcounter=0;
818+
#endif
812819

813820
*foundPtr= false;
814821

‎contrib/pg_pathman/rangeset.c‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -52,7 +52,7 @@ irange_list_union(List *a, List *b)
5252

5353
while (ca||cb)
5454
{
55-
IndexRangenext;
55+
IndexRangenext=0;
5656

5757
/* Fetch next range with lesser lower bound */
5858
if (ca&&cb)

‎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