Skip to content

Commit a0515ba

Browse files
zilderzilder
committed
pathman: some tests added
1 parent 47471b1 commit a0515ba

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
id SERIAL PRIMARY KEY,
1414
dt TIMESTAMP,
1515
txt TEXT);
16+
CREATE INDEX ON test.range_rel (dt);
1617
SELECT pathman.create_range_partitions('test.range_rel', 'dt', '2015-01-01'::DATE, '1 month'::INTERVAL, 3);
1718

1819
CREATE TABLE test.num_range_rel (
1920
id SERIAL PRIMARY KEY,
2021
txt TEXT);
2122
SELECT pathman.create_range_partitions('test.num_range_rel', 'id', 0, 1000, 3);
22-
INSERT INTO test.num_range_rel SELECT g, md5(g::TEXT) FROM generate_series(1, 3000) as g;
23-
VACUUM;
23+
24+
INSERT INTO test.num_range_rel
25+
SELECT g, md5(g::TEXT) FROM generate_series(1, 3000) as g;
26+
27+
INSERT INTO test.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 INTO test.hash_rel VALUES (1, 1);
2631
INSERT INTO test.hash_rel VALUES (2, 2);
@@ -29,6 +34,8 @@ INSERT INTO test.hash_rel VALUES (4, 4);
2934
INSERT INTO test.hash_rel VALUES (5, 5);
3035
INSERT INTO test.hash_rel VALUES (6, 6);
3136

37+
VACUUM;
38+
3239
/* update triggers test */
3340
SELECT pathman.create_hash_update_trigger('test.hash_rel');
3441
UPDATE test.hash_rel SET value = 7 WHERE value = 6;
@@ -51,6 +58,11 @@ EXPLAIN (COSTS OFF) SELECT * FROM test.num_range_rel WHERE id > 2500;
5158
EXPLAIN (COSTS OFF) SELECT * FROM test.num_range_rel WHERE id >= 1000 AND id < 3000;
5259
EXPLAIN (COSTS OFF) SELECT * FROM test.num_range_rel WHERE id >= 1500 AND id < 2500;
5360
EXPLAIN (COSTS OFF) SELECT * FROM test.num_range_rel WHERE (id >= 500 AND id < 1500) OR (id > 2500);
61+
EXPLAIN (COSTS OFF) SELECT * FROM test.range_rel WHERE dt > '2015-02-15';
62+
EXPLAIN (COSTS OFF) SELECT * FROM test.range_rel WHERE dt >= '2015-02-01' AND dt < '2015-03-01';
63+
EXPLAIN (COSTS OFF) SELECT * FROM test.range_rel WHERE dt >= '2015-02-15' AND dt < '2015-03-15';
64+
EXPLAIN (COSTS OFF) SELECT * FROM test.range_rel WHERE (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 * FROM test.num_range_rel WHERE id >= 1000 AND id < 3000;
6476
EXPLAIN (COSTS OFF) SELECT * FROM test.num_range_rel WHERE id >= 1500 AND id < 2500;
6577
EXPLAIN (COSTS OFF) SELECT * FROM test.num_range_rel WHERE (id >= 500 AND id < 1500) OR (id > 2500);
78+
EXPLAIN (COSTS OFF) SELECT * FROM test.range_rel WHERE dt > '2015-02-15';
79+
EXPLAIN (COSTS OFF) SELECT * FROM test.range_rel WHERE dt >= '2015-02-01' AND dt < '2015-03-01';
80+
EXPLAIN (COSTS OFF) SELECT * FROM test.range_rel WHERE dt >= '2015-02-15' AND dt < '2015-03-15';
81+
EXPLAIN (COSTS OFF) SELECT * FROM test.range_rel WHERE (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)