Skip to content

Commit 484ec05

Browse files
zilderzilder
committed
pathman: use of comparison functions between different types
1 parent 4d8be00 commit 484ec05

File tree

3 files changed

+134
-188
lines changed

3 files changed

+134
-188
lines changed

‎contrib/pg_pathman/expected/pg_pathman.out

Lines changed: 58 additions & 116 deletions
Original file line numberDiff line numberDiff line change
@@ -70,9 +70,7 @@ EXPLAIN (COSTS OFF) SELECT * FROM test.hash_rel WHERE value = 7;
7070
Append
7171
-> Seq Scan on hash_rel_1
7272
Filter: (value = 7)
73-
-> Seq Scan on hash_rel
74-
Filter: (value = 7)
75-
(5 rows)
73+
(3 rows)
7674

7775
SELECT * FROM test.hash_rel WHERE value = 7;
7876
id | value
@@ -93,9 +91,7 @@ EXPLAIN (COSTS OFF) SELECT * FROM test.num_range_rel WHERE id = 3001;
9391
Append
9492
-> Seq Scan on num_range_rel_4
9593
Filter: (id = 3001)
96-
-> Seq Scan on num_range_rel
97-
Filter: (id = 3001)
98-
(5 rows)
94+
(3 rows)
9995

10096
SELECT * FROM test.num_range_rel WHERE id = 3001;
10197
id | txt
@@ -113,30 +109,25 @@ EXPLAIN (COSTS OFF) SELECT * FROM test.hash_rel;
113109
-> Seq Scan on hash_rel_0
114110
-> Seq Scan on hash_rel_1
115111
-> Seq Scan on hash_rel_2
116-
-> Seq Scan on hash_rel
117-
(5 rows)
112+
(4 rows)
118113

119114
EXPLAIN (COSTS OFF) SELECT * FROM test.hash_rel WHERE value = 2;
120115
QUERY PLAN
121116
------------------------------
122117
Append
123118
-> Seq Scan on hash_rel_2
124119
Filter: (value = 2)
125-
-> Seq Scan on hash_rel
126-
Filter: (value = 2)
127-
(5 rows)
120+
(3 rows)
128121

129122
EXPLAIN (COSTS OFF) SELECT * FROM test.hash_rel WHERE value = 2 OR value = 1;
130-
QUERY PLAN
131-
----------------------------------------------
123+
QUERY PLAN
124+
------------------------------
132125
Append
133126
-> Seq Scan on hash_rel_1
134127
Filter: (value = 1)
135128
-> Seq Scan on hash_rel_2
136129
Filter: (value = 2)
137-
-> Seq Scan on hash_rel
138-
Filter: ((value = 2) OR (value = 1))
139-
(7 rows)
130+
(5 rows)
140131

141132
EXPLAIN (COSTS OFF) SELECT * FROM test.num_range_rel WHERE id > 2500;
142133
QUERY PLAN
@@ -145,35 +136,29 @@ EXPLAIN (COSTS OFF) SELECT * FROM test.num_range_rel WHERE id > 2500;
145136
-> Seq Scan on num_range_rel_3
146137
Filter: (id > 2500)
147138
-> Seq Scan on num_range_rel_4
148-
-> Seq Scan on num_range_rel
149-
Filter: (id > 2500)
150-
(6 rows)
139+
(4 rows)
151140

152141
EXPLAIN (COSTS OFF) SELECT * FROM test.num_range_rel WHERE id >= 1000 AND id < 3000;
153-
QUERY PLAN
154-
------------------------------------------------
142+
QUERY PLAN
143+
-----------------------------------
155144
Append
156145
-> Seq Scan on num_range_rel_2
157146
-> Seq Scan on num_range_rel_3
158-
-> Seq Scan on num_range_rel
159-
Filter: ((id >= 1000) AND (id < 3000))
160-
(5 rows)
147+
(3 rows)
161148

162149
EXPLAIN (COSTS OFF) SELECT * FROM test.num_range_rel WHERE id >= 1500 AND id < 2500;
163-
QUERY PLAN
164-
------------------------------------------------
150+
QUERY PLAN
151+
-----------------------------------
165152
Append
166153
-> Seq Scan on num_range_rel_2
167154
Filter: (id >= 1500)
168155
-> Seq Scan on num_range_rel_3
169156
Filter: (id < 2500)
170-
-> Seq Scan on num_range_rel
171-
Filter: ((id >= 1500) AND (id < 2500))
172-
(7 rows)
157+
(5 rows)
173158

174159
EXPLAIN (COSTS OFF) SELECT * FROM test.num_range_rel WHERE (id >= 500 AND id < 1500) OR (id > 2500);
175-
QUERY PLAN
176-
----------------------------------------------------------------
160+
QUERY PLAN
161+
-----------------------------------
177162
Append
178163
-> Seq Scan on num_range_rel_1
179164
Filter: (id >= 500)
@@ -182,9 +167,7 @@ EXPLAIN (COSTS OFF) SELECT * FROM test.num_range_rel WHERE (id >= 500 AND id < 1
182167
-> Seq Scan on num_range_rel_3
183168
Filter: (id > 2500)
184169
-> Seq Scan on num_range_rel_4
185-
-> Seq Scan on num_range_rel
186-
Filter: (((id >= 500) AND (id < 1500)) OR (id > 2500))
187-
(10 rows)
170+
(8 rows)
188171

189172
EXPLAIN (COSTS OFF) SELECT * FROM test.range_rel WHERE dt > '2015-02-15';
190173
QUERY PLAN
@@ -194,34 +177,28 @@ EXPLAIN (COSTS OFF) SELECT * FROM test.range_rel WHERE dt > '2015-02-15';
194177
Filter: (dt > 'Sun Feb 15 00:00:00 2015'::timestamp without time zone)
195178
-> Seq Scan on range_rel_3
196179
-> Seq Scan on range_rel_4
197-
-> Seq Scan on range_rel
198-
Filter: (dt > 'Sun Feb 15 00:00:00 2015'::timestamp without time zone)
199-
(7 rows)
180+
(5 rows)
200181

201182
EXPLAIN (COSTS OFF) SELECT * FROM test.range_rel WHERE dt >= '2015-02-01' AND dt < '2015-03-01';
202-
QUERY PLAN
203-
------------------------------------------------------------------------------------------------------------------------------------------------------
183+
QUERY PLAN
184+
-------------------------------
204185
Append
205186
-> Seq Scan on range_rel_2
206-
-> Seq Scan on range_rel
207-
Filter: ((dt >= 'Sun Feb 01 00:00:00 2015'::timestamp without time zone) AND (dt < 'Sun Mar 01 00:00:00 2015'::timestamp without time zone))
208-
(4 rows)
187+
(2 rows)
209188

210189
EXPLAIN (COSTS OFF) SELECT * FROM test.range_rel WHERE dt >= '2015-02-15' AND dt < '2015-03-15';
211-
QUERY PLAN
212-
------------------------------------------------------------------------------------------------------------------------------------------------------
190+
QUERY PLAN
191+
---------------------------------------------------------------------------------
213192
Append
214193
-> Seq Scan on range_rel_2
215194
Filter: (dt >= 'Sun Feb 15 00:00:00 2015'::timestamp without time zone)
216195
-> Seq Scan on range_rel_3
217196
Filter: (dt < 'Sun Mar 15 00:00:00 2015'::timestamp without time zone)
218-
-> Seq Scan on range_rel
219-
Filter: ((dt >= 'Sun Feb 15 00:00:00 2015'::timestamp without time zone) AND (dt < 'Sun Mar 15 00:00:00 2015'::timestamp without time zone))
220-
(7 rows)
197+
(5 rows)
221198

222199
EXPLAIN (COSTS OFF) SELECT * FROM test.range_rel WHERE (dt >= '2015-01-15' AND dt < '2015-02-15') OR (dt > '2015-03-15');
223-
QUERY PLAN
224-
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
200+
QUERY PLAN
201+
---------------------------------------------------------------------------------
225202
Append
226203
-> Seq Scan on range_rel_1
227204
Filter: (dt >= 'Thu Jan 15 00:00:00 2015'::timestamp without time zone)
@@ -230,9 +207,7 @@ EXPLAIN (COSTS OFF) SELECT * FROM test.range_rel WHERE (dt >= '2015-01-15' AND d
230207
-> Seq Scan on range_rel_3
231208
Filter: (dt > 'Sun Mar 15 00:00:00 2015'::timestamp without time zone)
232209
-> Seq Scan on range_rel_4
233-
-> Seq Scan on range_rel
234-
Filter: (((dt >= 'Thu Jan 15 00:00:00 2015'::timestamp without time zone) AND (dt < 'Sun Feb 15 00:00:00 2015'::timestamp without time zone)) OR (dt > 'Sun Mar 15 00:00:00 2015'::timestamp without time zone))
235-
(10 rows)
210+
(8 rows)
236211

237212
SET enable_indexscan = ON;
238213
SET enable_bitmapscan = OFF;
@@ -244,30 +219,25 @@ EXPLAIN (COSTS OFF) SELECT * FROM test.hash_rel;
244219
-> Seq Scan on hash_rel_0
245220
-> Seq Scan on hash_rel_1
246221
-> Seq Scan on hash_rel_2
247-
-> Seq Scan on hash_rel
248-
(5 rows)
222+
(4 rows)
249223

250224
EXPLAIN (COSTS OFF) SELECT * FROM test.hash_rel WHERE value = 2;
251225
QUERY PLAN
252226
------------------------------
253227
Append
254228
-> Seq Scan on hash_rel_2
255229
Filter: (value = 2)
256-
-> Seq Scan on hash_rel
257-
Filter: (value = 2)
258-
(5 rows)
230+
(3 rows)
259231

260232
EXPLAIN (COSTS OFF) SELECT * FROM test.hash_rel WHERE value = 2 OR value = 1;
261-
QUERY PLAN
262-
----------------------------------------------
233+
QUERY PLAN
234+
------------------------------
263235
Append
264236
-> Seq Scan on hash_rel_1
265237
Filter: (value = 1)
266238
-> Seq Scan on hash_rel_2
267239
Filter: (value = 2)
268-
-> Seq Scan on hash_rel
269-
Filter: ((value = 2) OR (value = 1))
270-
(7 rows)
240+
(5 rows)
271241

272242
EXPLAIN (COSTS OFF) SELECT * FROM test.num_range_rel WHERE id > 2500;
273243
QUERY PLAN
@@ -276,19 +246,15 @@ EXPLAIN (COSTS OFF) SELECT * FROM test.num_range_rel WHERE id > 2500;
276246
-> Index Scan using num_range_rel_3_pkey on num_range_rel_3
277247
Index Cond: (id > 2500)
278248
-> Seq Scan on num_range_rel_4
279-
-> Index Scan using num_range_rel_pkey on num_range_rel
280-
Index Cond: (id > 2500)
281-
(6 rows)
249+
(4 rows)
282250

283251
EXPLAIN (COSTS OFF) SELECT * FROM test.num_range_rel WHERE id >= 1000 AND id < 3000;
284-
QUERY PLAN
285-
------------------------------------------------------------
252+
QUERY PLAN
253+
-----------------------------------
286254
Append
287255
-> Seq Scan on num_range_rel_2
288256
-> Seq Scan on num_range_rel_3
289-
-> Index Scan using num_range_rel_pkey on num_range_rel
290-
Index Cond: ((id >= 1000) AND (id < 3000))
291-
(5 rows)
257+
(3 rows)
292258

293259
EXPLAIN (COSTS OFF) SELECT * FROM test.num_range_rel WHERE id >= 1500 AND id < 2500;
294260
QUERY PLAN
@@ -298,9 +264,7 @@ EXPLAIN (COSTS OFF) SELECT * FROM test.num_range_rel WHERE id >= 1500 AND id < 2
298264
Index Cond: (id >= 1500)
299265
-> Index Scan using num_range_rel_3_pkey on num_range_rel_3
300266
Index Cond: (id < 2500)
301-
-> Index Scan using num_range_rel_pkey on num_range_rel
302-
Index Cond: ((id >= 1500) AND (id < 2500))
303-
(7 rows)
267+
(5 rows)
304268

305269
EXPLAIN (COSTS OFF) SELECT * FROM test.num_range_rel WHERE (id >= 500 AND id < 1500) OR (id > 2500);
306270
QUERY PLAN
@@ -313,9 +277,7 @@ EXPLAIN (COSTS OFF) SELECT * FROM test.num_range_rel WHERE (id >= 500 AND id < 1
313277
-> Index Scan using num_range_rel_3_pkey on num_range_rel_3
314278
Index Cond: (id > 2500)
315279
-> Seq Scan on num_range_rel_4
316-
-> Seq Scan on num_range_rel
317-
Filter: (((id >= 500) AND (id < 1500)) OR (id > 2500))
318-
(10 rows)
280+
(8 rows)
319281

320282
EXPLAIN (COSTS OFF) SELECT * FROM test.range_rel WHERE dt > '2015-02-15';
321283
QUERY PLAN
@@ -325,34 +287,28 @@ EXPLAIN (COSTS OFF) SELECT * FROM test.range_rel WHERE dt > '2015-02-15';
325287
Index Cond: (dt > 'Sun Feb 15 00:00:00 2015'::timestamp without time zone)
326288
-> Seq Scan on range_rel_3
327289
-> Seq Scan on range_rel_4
328-
-> Index Scan using range_rel_dt_idx on range_rel
329-
Index Cond: (dt > 'Sun Feb 15 00:00:00 2015'::timestamp without time zone)
330-
(7 rows)
290+
(5 rows)
331291

332292
EXPLAIN (COSTS OFF) SELECT * FROM test.range_rel WHERE dt >= '2015-02-01' AND dt < '2015-03-01';
333-
QUERY PLAN
334-
----------------------------------------------------------------------------------------------------------------------------------------------------------
293+
QUERY PLAN
294+
-------------------------------
335295
Append
336296
-> Seq Scan on range_rel_2
337-
-> Index Scan using range_rel_dt_idx on range_rel
338-
Index Cond: ((dt >= 'Sun Feb 01 00:00:00 2015'::timestamp without time zone) AND (dt < 'Sun Mar 01 00:00:00 2015'::timestamp without time zone))
339-
(4 rows)
297+
(2 rows)
340298

341299
EXPLAIN (COSTS OFF) SELECT * FROM test.range_rel WHERE dt >= '2015-02-15' AND dt < '2015-03-15';
342-
QUERY PLAN
343-
----------------------------------------------------------------------------------------------------------------------------------------------------------
300+
QUERY PLAN
301+
-------------------------------------------------------------------------------------
344302
Append
345303
-> Index Scan using range_rel_2_dt_idx on range_rel_2
346304
Index Cond: (dt >= 'Sun Feb 15 00:00:00 2015'::timestamp without time zone)
347305
-> Index Scan using range_rel_3_dt_idx on range_rel_3
348306
Index Cond: (dt < 'Sun Mar 15 00:00:00 2015'::timestamp without time zone)
349-
-> Index Scan using range_rel_dt_idx on range_rel
350-
Index Cond: ((dt >= 'Sun Feb 15 00:00:00 2015'::timestamp without time zone) AND (dt < 'Sun Mar 15 00:00:00 2015'::timestamp without time zone))
351-
(7 rows)
307+
(5 rows)
352308

353309
EXPLAIN (COSTS OFF) SELECT * FROM test.range_rel WHERE (dt >= '2015-01-15' AND dt < '2015-02-15') OR (dt > '2015-03-15');
354-
QUERY PLAN
355-
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
310+
QUERY PLAN
311+
-------------------------------------------------------------------------------------
356312
Append
357313
-> Index Scan using range_rel_1_dt_idx on range_rel_1
358314
Index Cond: (dt >= 'Thu Jan 15 00:00:00 2015'::timestamp without time zone)
@@ -361,9 +317,7 @@ EXPLAIN (COSTS OFF) SELECT * FROM test.range_rel WHERE (dt >= '2015-01-15' AND d
361317
-> Index Scan using range_rel_3_dt_idx on range_rel_3
362318
Index Cond: (dt > 'Sun Mar 15 00:00:00 2015'::timestamp without time zone)
363319
-> Seq Scan on range_rel_4
364-
-> Seq Scan on range_rel
365-
Filter: (((dt >= 'Thu Jan 15 00:00:00 2015'::timestamp without time zone) AND (dt < 'Sun Feb 15 00:00:00 2015'::timestamp without time zone)) OR (dt > 'Sun Mar 15 00:00:00 2015'::timestamp without time zone))
366-
(10 rows)
320+
(8 rows)
367321

368322
/*
369323
* Test split and merge
@@ -387,9 +341,7 @@ EXPLAIN (COSTS OFF) SELECT * FROM test.num_range_rel WHERE id BETWEEN 100 AND 70
387341
Index Cond: (id >= 100)
388342
-> Index Scan using num_range_rel_5_pkey on num_range_rel_5
389343
Index Cond: (id <= 700)
390-
-> Index Scan using num_range_rel_pkey on num_range_rel
391-
Index Cond: ((id >= 100) AND (id <= 700))
392-
(7 rows)
344+
(5 rows)
393345

394346
SELECT pathman.split_range_partition('test.range_rel_1', '2015-01-15'::DATE);
395347
NOTICE: Creating new partition...
@@ -418,9 +370,7 @@ EXPLAIN (COSTS OFF) SELECT * FROM test.num_range_rel WHERE id BETWEEN 100 AND 70
418370
Append
419371
-> Index Scan using num_range_rel_1_pkey on num_range_rel_1
420372
Index Cond: ((id >= 100) AND (id <= 700))
421-
-> Index Scan using num_range_rel_pkey on num_range_rel
422-
Index Cond: ((id >= 100) AND (id <= 700))
423-
(5 rows)
373+
(3 rows)
424374

425375
SELECT pathman.merge_range_partitions('test.range_rel_1', 'test.range_rel_' || currval('test.range_rel_seq'));
426376
NOTICE: Altering first partition...
@@ -531,9 +481,7 @@ EXPLAIN (COSTS OFF) SELECT * FROM hash_rel WHERE id = 1234;
531481
Index Cond: (id = 1234)
532482
-> Index Scan using hash_rel_2_pkey on hash_rel_2
533483
Index Cond: (id = 1234)
534-
-> Index Scan using hash_rel_pkey on hash_rel
535-
Index Cond: (id = 1234)
536-
(9 rows)
484+
(7 rows)
537485

538486
/* Range */
539487
CREATE TABLE range_rel (
@@ -602,24 +550,20 @@ NOTICE: Done!
602550
(1 row)
603551

604552
EXPLAIN (COSTS OFF) SELECT * FROM range_rel WHERE dt < '2010-03-01';
605-
QUERY PLAN
606-
--------------------------------------------------------------------------------
553+
QUERY PLAN
554+
--------------------------------
607555
Append
608556
-> Seq Scan on range_rel_1
609557
-> Seq Scan on range_rel_13
610-
-> Seq Scan on range_rel
611-
Filter: (dt < 'Mon Mar 01 00:00:00 2010'::timestamp without time zone)
612-
(5 rows)
558+
(3 rows)
613559

614560
EXPLAIN (COSTS OFF) SELECT * FROM range_rel WHERE dt > '2010-12-15';
615561
QUERY PLAN
616562
--------------------------------------------------------------------------------
617563
Append
618564
-> Seq Scan on range_rel_12
619565
Filter: (dt > 'Wed Dec 15 00:00:00 2010'::timestamp without time zone)
620-
-> Seq Scan on range_rel
621-
Filter: (dt > 'Wed Dec 15 00:00:00 2010'::timestamp without time zone)
622-
(5 rows)
566+
(3 rows)
623567

624568
/* Manual partitions creation */
625569
CREATE TABLE range_rel_archive (CHECK (dt >= '2000-01-01' AND dt < '2005-01-01')) INHERITS (range_rel);
@@ -630,15 +574,13 @@ SELECT on_update_partitions('range_rel'::regclass::oid);
630574
(1 row)
631575

632576
EXPLAIN (COSTS OFF) SELECT * FROM range_rel WHERE dt < '2010-03-01';
633-
QUERY PLAN
634-
--------------------------------------------------------------------------------
577+
QUERY PLAN
578+
-------------------------------------
635579
Append
636580
-> Seq Scan on range_rel_archive
637581
-> Seq Scan on range_rel_15
638582
-> Seq Scan on range_rel_1
639583
-> Seq Scan on range_rel_13
640-
-> Seq Scan on range_rel
641-
Filter: (dt < 'Mon Mar 01 00:00:00 2010'::timestamp without time zone)
642-
(7 rows)
584+
(5 rows)
643585

644586
DROP EXTENSION pg_pathman;

0 commit comments

Comments
 (0)