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

Commita0515ba

Browse files
committed
pathman: some tests added
1 parent47471b1 commita0515ba

File tree

2 files changed

+104
-4
lines changed

2 files changed

+104
-4
lines changed

‎contrib/pg_pathman/expected/pg_pathman.out‎

Lines changed: 86 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -17,6 +17,7 @@ CREATE TABLE test.range_rel (
1717
id SERIAL PRIMARY KEY,
1818
dt TIMESTAMP,
1919
txt TEXT);
20+
CREATE INDEX ON test.range_rel (dt);
2021
SELECT pathman.create_range_partitions('test.range_rel', 'dt', '2015-01-01'::DATE, '1 month'::INTERVAL, 3);
2122
NOTICE: sequence "range_rel_seq" does not exist, skipping
2223
create_range_partitions
@@ -34,14 +35,17 @@ NOTICE: sequence "num_range_rel_seq" does not exist, skipping
3435

3536
(1 row)
3637

37-
INSERT INTO test.num_range_rel SELECT g, md5(g::TEXT) FROM generate_series(1, 3000) as g;
38-
VACUUM;
38+
INSERT INTO test.num_range_rel
39+
SELECT g, md5(g::TEXT) FROM generate_series(1, 3000) as g;
40+
INSERT INTO test.range_rel (dt, txt)
41+
SELECT g, md5(g::TEXT) FROM generate_series('2015-01-01', '2015-04-30', '1 day'::interval) as g;
3942
INSERT INTO test.hash_rel VALUES (1, 1);
4043
INSERT INTO test.hash_rel VALUES (2, 2);
4144
INSERT INTO test.hash_rel VALUES (3, 3);
4245
INSERT INTO test.hash_rel VALUES (4, 4);
4346
INSERT INTO test.hash_rel VALUES (5, 5);
4447
INSERT INTO test.hash_rel VALUES (6, 6);
48+
VACUUM;
4549
/* update triggers test */
4650
SELECT pathman.create_hash_update_trigger('test.hash_rel');
4751
create_hash_update_trigger
@@ -155,6 +159,46 @@ EXPLAIN (COSTS OFF) SELECT * FROM test.num_range_rel WHERE (id >= 500 AND id < 1
155159
-> Seq Scan on num_range_rel_4
156160
(8 rows)
157161

162+
EXPLAIN (COSTS OFF) SELECT * FROM test.range_rel WHERE dt > '2015-02-15';
163+
QUERY PLAN
164+
--------------------------------------------------------------------------------
165+
Append
166+
-> Seq Scan on range_rel_2
167+
Filter: (dt > 'Sun Feb 15 00:00:00 2015'::timestamp without time zone)
168+
-> Seq Scan on range_rel_3
169+
-> Seq Scan on range_rel_4
170+
(5 rows)
171+
172+
EXPLAIN (COSTS OFF) SELECT * FROM test.range_rel WHERE dt >= '2015-02-01' AND dt < '2015-03-01';
173+
QUERY PLAN
174+
-------------------------------
175+
Append
176+
-> Seq Scan on range_rel_2
177+
(2 rows)
178+
179+
EXPLAIN (COSTS OFF) SELECT * FROM test.range_rel WHERE dt >= '2015-02-15' AND dt < '2015-03-15';
180+
QUERY PLAN
181+
---------------------------------------------------------------------------------
182+
Append
183+
-> Seq Scan on range_rel_2
184+
Filter: (dt >= 'Sun Feb 15 00:00:00 2015'::timestamp without time zone)
185+
-> Seq Scan on range_rel_3
186+
Filter: (dt < 'Sun Mar 15 00:00:00 2015'::timestamp without time zone)
187+
(5 rows)
188+
189+
EXPLAIN (COSTS OFF) SELECT * FROM test.range_rel WHERE (dt >= '2015-01-15' AND dt < '2015-02-15') OR (dt > '2015-03-15');
190+
QUERY PLAN
191+
---------------------------------------------------------------------------------
192+
Append
193+
-> Seq Scan on range_rel_1
194+
Filter: (dt >= 'Thu Jan 15 00:00:00 2015'::timestamp without time zone)
195+
-> Seq Scan on range_rel_2
196+
Filter: (dt < 'Sun Feb 15 00:00:00 2015'::timestamp without time zone)
197+
-> Seq Scan on range_rel_3
198+
Filter: (dt > 'Sun Mar 15 00:00:00 2015'::timestamp without time zone)
199+
-> Seq Scan on range_rel_4
200+
(8 rows)
201+
158202
SET enable_indexscan = ON;
159203
SET enable_bitmapscan = OFF;
160204
SET enable_seqscan = OFF;
@@ -225,6 +269,46 @@ EXPLAIN (COSTS OFF) SELECT * FROM test.num_range_rel WHERE (id >= 500 AND id < 1
225269
-> Seq Scan on num_range_rel_4
226270
(8 rows)
227271

272+
EXPLAIN (COSTS OFF) SELECT * FROM test.range_rel WHERE dt > '2015-02-15';
273+
QUERY PLAN
274+
------------------------------------------------------------------------------------
275+
Append
276+
-> Index Scan using range_rel_2_dt_idx on range_rel_2
277+
Index Cond: (dt > 'Sun Feb 15 00:00:00 2015'::timestamp without time zone)
278+
-> Seq Scan on range_rel_3
279+
-> Seq Scan on range_rel_4
280+
(5 rows)
281+
282+
EXPLAIN (COSTS OFF) SELECT * FROM test.range_rel WHERE dt >= '2015-02-01' AND dt < '2015-03-01';
283+
QUERY PLAN
284+
-------------------------------
285+
Append
286+
-> Seq Scan on range_rel_2
287+
(2 rows)
288+
289+
EXPLAIN (COSTS OFF) SELECT * FROM test.range_rel WHERE dt >= '2015-02-15' AND dt < '2015-03-15';
290+
QUERY PLAN
291+
-------------------------------------------------------------------------------------
292+
Append
293+
-> Index Scan using range_rel_2_dt_idx on range_rel_2
294+
Index Cond: (dt >= 'Sun Feb 15 00:00:00 2015'::timestamp without time zone)
295+
-> Index Scan using range_rel_3_dt_idx on range_rel_3
296+
Index Cond: (dt < 'Sun Mar 15 00:00:00 2015'::timestamp without time zone)
297+
(5 rows)
298+
299+
EXPLAIN (COSTS OFF) SELECT * FROM test.range_rel WHERE (dt >= '2015-01-15' AND dt < '2015-02-15') OR (dt > '2015-03-15');
300+
QUERY PLAN
301+
-------------------------------------------------------------------------------------
302+
Append
303+
-> Index Scan using range_rel_1_dt_idx on range_rel_1
304+
Index Cond: (dt >= 'Thu Jan 15 00:00:00 2015'::timestamp without time zone)
305+
-> Index Scan using range_rel_2_dt_idx on range_rel_2
306+
Index Cond: (dt < 'Sun Feb 15 00:00:00 2015'::timestamp without time zone)
307+
-> Index Scan using range_rel_3_dt_idx on range_rel_3
308+
Index Cond: (dt > 'Sun Mar 15 00:00:00 2015'::timestamp without time zone)
309+
-> Seq Scan on range_rel_4
310+
(8 rows)
311+
228312
/*
229313
* Test split and merge
230314
*/

‎contrib/pg_pathman/sql/pg_pathman.sql‎

Lines changed: 18 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -13,14 +13,19 @@ CREATE TABLE test.range_rel (
1313
idSERIALPRIMARY KEY,
1414
dtTIMESTAMP,
1515
txtTEXT);
16+
CREATEINDEXONtest.range_rel (dt);
1617
SELECTpathman.create_range_partitions('test.range_rel','dt','2015-01-01'::DATE,'1 month'::INTERVAL,3);
1718

1819
CREATETABLEtest.num_range_rel (
1920
idSERIALPRIMARY KEY,
2021
txtTEXT);
2122
SELECTpathman.create_range_partitions('test.num_range_rel','id',0,1000,3);
22-
INSERT INTOtest.num_range_relSELECT g, md5(g::TEXT)FROM generate_series(1,3000)as g;
23-
VACUUM;
23+
24+
INSERT INTOtest.num_range_rel
25+
SELECT g, md5(g::TEXT)FROM generate_series(1,3000)as g;
26+
27+
INSERT INTOtest.range_rel (dt, txt)
28+
SELECT g, md5(g::TEXT)FROM generate_series('2015-01-01','2015-04-30','1 day'::interval)as g;
2429

2530
INSERT INTOtest.hash_relVALUES (1,1);
2631
INSERT INTOtest.hash_relVALUES (2,2);
@@ -29,6 +34,8 @@ INSERT INTO test.hash_rel VALUES (4, 4);
2934
INSERT INTOtest.hash_relVALUES (5,5);
3035
INSERT INTOtest.hash_relVALUES (6,6);
3136

37+
VACUUM;
38+
3239
/* update triggers test*/
3340
SELECTpathman.create_hash_update_trigger('test.hash_rel');
3441
UPDATEtest.hash_relSET value=7WHERE value=6;
@@ -51,6 +58,11 @@ EXPLAIN (COSTS OFF) SELECT * FROM test.num_range_rel WHERE id > 2500;
5158
EXPLAIN (COSTS OFF)SELECT*FROMtest.num_range_relWHERE id>=1000AND id<3000;
5259
EXPLAIN (COSTS OFF)SELECT*FROMtest.num_range_relWHERE id>=1500AND id<2500;
5360
EXPLAIN (COSTS OFF)SELECT*FROMtest.num_range_relWHERE (id>=500AND id<1500)OR (id>2500);
61+
EXPLAIN (COSTS OFF)SELECT*FROMtest.range_relWHERE dt>'2015-02-15';
62+
EXPLAIN (COSTS OFF)SELECT*FROMtest.range_relWHERE dt>='2015-02-01'AND dt<'2015-03-01';
63+
EXPLAIN (COSTS OFF)SELECT*FROMtest.range_relWHERE dt>='2015-02-15'AND dt<'2015-03-15';
64+
EXPLAIN (COSTS OFF)SELECT*FROMtest.range_relWHERE (dt>='2015-01-15'AND dt<'2015-02-15')OR (dt>'2015-03-15');
65+
5466

5567
SET enable_indexscan=ON;
5668
SET enable_bitmapscan= OFF;
@@ -63,6 +75,10 @@ EXPLAIN (COSTS OFF) SELECT * FROM test.num_range_rel WHERE id > 2500;
6375
EXPLAIN (COSTS OFF)SELECT*FROMtest.num_range_relWHERE id>=1000AND id<3000;
6476
EXPLAIN (COSTS OFF)SELECT*FROMtest.num_range_relWHERE id>=1500AND id<2500;
6577
EXPLAIN (COSTS OFF)SELECT*FROMtest.num_range_relWHERE (id>=500AND id<1500)OR (id>2500);
78+
EXPLAIN (COSTS OFF)SELECT*FROMtest.range_relWHERE dt>'2015-02-15';
79+
EXPLAIN (COSTS OFF)SELECT*FROMtest.range_relWHERE dt>='2015-02-01'AND dt<'2015-03-01';
80+
EXPLAIN (COSTS OFF)SELECT*FROMtest.range_relWHERE dt>='2015-02-15'AND dt<'2015-03-15';
81+
EXPLAIN (COSTS OFF)SELECT*FROMtest.range_relWHERE (dt>='2015-01-15'AND dt<'2015-02-15')OR (dt>'2015-03-15');
6682

6783
/*
6884
* Test split and merge

0 commit comments

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