@@ -10,9 +10,16 @@ CREATE EXTENSION pg_pathman SCHEMA pathman;
1010CREATE SCHEMA test;
1111SET enable_indexscan = ON;
1212SET enable_seqscan = OFF;
13- /* Temporary table for JOINs */
13+ /* Temporary tables for JOINs */
1414CREATE TABLE test.tmp (id INTEGER NOT NULL, value INTEGER NOT NULL);
1515INSERT INTO test.tmp VALUES (1, 1), (2, 2);
16+ CREATE TABLE test.tmp2 (id INTEGER NOT NULL, value INTEGER NOT NULL);
17+ SELECT pathman.create_range_partitions('test.tmp2', 'id', 1, 1, 10);
18+ create_range_partitions
19+ -------------------------
20+ 10
21+ (1 row)
22+
1623/* Partition table by RANGE */
1724CREATE TABLE test.range_rel (
1825 id SERIAL PRIMARY KEY,
@@ -28,6 +35,7 @@ SELECT pathman.create_range_partitions('test.range_rel', 'dt',
2835 12
2936(1 row)
3037
38+ VACUUM ANALYZE;
3139/*
3240 * Test UPDATE and DELETE
3341 */
@@ -104,16 +112,15 @@ ROLLBACK;
104112EXPLAIN (COSTS OFF)
105113UPDATE test.range_rel r SET value = t.value
106114FROM test.tmp t WHERE r.dt = '2010-01-01' AND r.id = t.id;
107- QUERY PLAN
108- --------------------------------------------------------------------------------------------
115+ QUERY PLAN
116+ --------------------------------------------------------------------------------------
109117 Update on range_rel_1 r
110- -> Hash Join
111- Hash Cond: (t.id = r.id)
118+ -> Nested Loop
119+ Join Filter: (r.id = t.id)
120+ -> Index Scan using range_rel_1_pkey on range_rel_1 r
121+ Filter: (dt = 'Fri Jan 01 00:00:00 2010'::timestamp without time zone)
112122 -> Seq Scan on tmp t
113- -> Hash
114- -> Index Scan using range_rel_1_pkey on range_rel_1 r
115- Filter: (dt = 'Fri Jan 01 00:00:00 2010'::timestamp without time zone)
116- (7 rows)
123+ (6 rows)
117124
118125BEGIN;
119126UPDATE test.range_rel r SET value = t.value
@@ -123,19 +130,21 @@ ROLLBACK;
123130EXPLAIN (COSTS OFF)
124131UPDATE test.tmp t SET value = r.value
125132FROM test.range_rel r WHERE r.dt = '2010-01-01' AND r.id = t.id;
126- QUERY PLAN
127- --------------------------------------------------------------------------------------------------
133+ QUERY PLAN
134+ --------------------------------------------------------------------------------------------
128135 Update on tmp t
129- -> Hash Join
130- Hash Cond: (t.id = r.id)
131- -> Seq Scan on tmp t
132- -> Hash
133- -> Append
134- -> Index Scan using range_rel_pkey on range_rel r
135- Filter: (dt = 'Fri Jan 01 00:00:00 2010'::timestamp without time zone)
136- -> Index Scan using range_rel_1_pkey on range_rel_1 r_1
137- Filter: (dt = 'Fri Jan 01 00:00:00 2010'::timestamp without time zone)
138- (10 rows)
136+ -> Merge Join
137+ Merge Cond: (r.id = t.id)
138+ -> Merge Append
139+ Sort Key: r.id
140+ -> Index Scan using range_rel_pkey on range_rel r
141+ Filter: (dt = 'Fri Jan 01 00:00:00 2010'::timestamp without time zone)
142+ -> Index Scan using range_rel_1_pkey on range_rel_1 r_1
143+ Filter: (dt = 'Fri Jan 01 00:00:00 2010'::timestamp without time zone)
144+ -> Sort
145+ Sort Key: t.id
146+ -> Seq Scan on tmp t
147+ (12 rows)
139148
140149BEGIN;
141150UPDATE test.tmp t SET value = r.value
@@ -145,16 +154,15 @@ ROLLBACK;
145154EXPLAIN (COSTS OFF)
146155DELETE FROM test.range_rel r USING test.tmp t
147156WHERE r.dt = '2010-01-02' AND r.id = t.id;
148- QUERY PLAN
149- --------------------------------------------------------------------------------------------
157+ QUERY PLAN
158+ --------------------------------------------------------------------------------------
150159 Delete on range_rel_1 r
151- -> Hash Join
152- Hash Cond: (t.id = r.id)
160+ -> Nested Loop
161+ Join Filter: (r.id = t.id)
162+ -> Index Scan using range_rel_1_pkey on range_rel_1 r
163+ Filter: (dt = 'Sat Jan 02 00:00:00 2010'::timestamp without time zone)
153164 -> Seq Scan on tmp t
154- -> Hash
155- -> Index Scan using range_rel_1_pkey on range_rel_1 r
156- Filter: (dt = 'Sat Jan 02 00:00:00 2010'::timestamp without time zone)
157- (7 rows)
165+ (6 rows)
158166
159167BEGIN;
160168DELETE FROM test.range_rel r USING test.tmp t
@@ -164,24 +172,98 @@ ROLLBACK;
164172EXPLAIN (COSTS OFF)
165173DELETE FROM test.tmp t USING test.range_rel r
166174WHERE r.dt = '2010-01-02' AND r.id = t.id;
167- QUERY PLAN
168- --------------------------------------------------------------------------------------------------
175+ QUERY PLAN
176+ --------------------------------------------------------------------------------------------
169177 Delete on tmp t
170- -> Hash Join
171- Hash Cond: (t.id = r.id)
172- -> Seq Scan on tmp t
173- -> Hash
174- -> Append
175- -> Index Scan using range_rel_pkey on range_rel r
176- Filter: (dt = 'Sat Jan 02 00:00:00 2010'::timestamp without time zone)
177- -> Index Scan using range_rel_1_pkey on range_rel_1 r_1
178- Filter: (dt = 'Sat Jan 02 00:00:00 2010'::timestamp without time zone)
179- (10 rows)
178+ -> Merge Join
179+ Merge Cond: (r.id = t.id)
180+ -> Merge Append
181+ Sort Key: r.id
182+ -> Index Scan using range_rel_pkey on range_rel r
183+ Filter: (dt = 'Sat Jan 02 00:00:00 2010'::timestamp without time zone)
184+ -> Index Scan using range_rel_1_pkey on range_rel_1 r_1
185+ Filter: (dt = 'Sat Jan 02 00:00:00 2010'::timestamp without time zone)
186+ -> Sort
187+ Sort Key: t.id
188+ -> Seq Scan on tmp t
189+ (12 rows)
180190
181191BEGIN;
182192DELETE FROM test.tmp t USING test.range_rel r
183193WHERE r.dt = '2010-01-02' AND r.id = t.id;
184194ROLLBACK;
195+ /* DELETE + USING, two partitioned tables */
196+ EXPLAIN (COSTS OFF)
197+ DELETE FROM test.range_rel r USING test.tmp2 t
198+ WHERE t.id = r.id;
199+ ERROR: DELETE and UPDATE queries with a join of partitioned tables are not supported
200+ BEGIN;
201+ DELETE FROM test.range_rel r USING test.tmp2 t
202+ WHERE t.id = r.id;
203+ ERROR: DELETE and UPDATE queries with a join of partitioned tables are not supported
204+ ROLLBACK;
205+ /* DELETE + USING, partitioned table + two partitioned tables in subselect */
206+ EXPLAIN (COSTS OFF)
207+ DELETE FROM test.range_rel r
208+ USING (SELECT *
209+ FROM test.tmp2 a1
210+ JOIN test.tmp2 a2
211+ USING(id)) t
212+ WHERE t.id = r.id;
213+ ERROR: DELETE and UPDATE queries with a join of partitioned tables are not supported
214+ BEGIN;
215+ DELETE FROM test.range_rel r
216+ USING (SELECT *
217+ FROM test.tmp2 a1
218+ JOIN test.tmp2 a2
219+ USING(id)) t
220+ WHERE t.id = r.id;
221+ ERROR: DELETE and UPDATE queries with a join of partitioned tables are not supported
222+ ROLLBACK;
223+ /* DELETE + USING, single table + two partitioned tables in subselect */
224+ EXPLAIN (COSTS OFF)
225+ DELETE FROM test.tmp r
226+ USING (SELECT *
227+ FROM test.tmp2 a1
228+ JOIN test.tmp2 a2
229+ USING(id)) t
230+ WHERE t.id = r.id;
231+ QUERY PLAN
232+ ---------------------------------------------
233+ Delete on tmp r
234+ -> Merge Join
235+ Merge Cond: (a1.id = a2.id)
236+ -> Merge Join
237+ Merge Cond: (r.id = a1.id)
238+ -> Sort
239+ Sort Key: r.id
240+ -> Seq Scan on tmp r
241+ -> Sort
242+ Sort Key: a1.id
243+ -> Seq Scan on tmp2 a1
244+ -> Sort
245+ Sort Key: a2.id
246+ -> Seq Scan on tmp2 a2
247+ (14 rows)
248+
249+ BEGIN;
250+ DELETE FROM test.tmp r
251+ USING (SELECT *
252+ FROM test.tmp2 a1
253+ JOIN test.tmp2 a2
254+ USING(id)) t
255+ WHERE t.id = r.id;
256+ ROLLBACK;
257+ /* UPDATE + FROM, two partitioned tables */
258+ EXPLAIN (COSTS OFF)
259+ UPDATE test.range_rel r SET value = 1 FROM test.tmp2 t
260+ WHERE t.id = r.id;
261+ ERROR: DELETE and UPDATE queries with a join of partitioned tables are not supported
262+ BEGIN;
263+ UPDATE test.range_rel r SET value = 1 FROM test.tmp2 t
264+ WHERE t.id = r.id;
265+ ERROR: DELETE and UPDATE queries with a join of partitioned tables are not supported
266+ ROLLBACK;
185267/* Test special rule for CTE; SELECT (PostgreSQL 9.5) */
186268EXPLAIN (COSTS OFF)
187269WITH q AS (SELECT * FROM test.range_rel r
@@ -195,10 +277,9 @@ DELETE FROM test.tmp USING q;
195277 -> Seq Scan on range_rel_1 r
196278 Filter: (dt = 'Sat Jan 02 00:00:00 2010'::timestamp without time zone)
197279 -> Nested Loop
280+ -> Seq Scan on tmp
198281 -> CTE Scan on q
199- -> Materialize
200- -> Seq Scan on tmp
201- (9 rows)
282+ (8 rows)
202283
203284BEGIN;
204285WITH q AS (SELECT * FROM test.range_rel r
@@ -219,10 +300,9 @@ DELETE FROM test.tmp USING q;
219300 -> Seq Scan on range_rel_1 r
220301 Filter: (dt = 'Sat Jan 02 00:00:00 2010'::timestamp without time zone)
221302 -> Nested Loop
303+ -> Seq Scan on tmp
222304 -> CTE Scan on q
223- -> Materialize
224- -> Seq Scan on tmp
225- (9 rows)
305+ (8 rows)
226306
227307BEGIN;
228308WITH q AS (DELETE FROM test.range_rel r
@@ -237,25 +317,26 @@ WITH q AS (DELETE FROM test.tmp t
237317 WHERE r.dt = '2010-01-02' AND r.id = t.id
238318 RETURNING *)
239319DELETE FROM test.tmp USING q;
240- QUERY PLAN
241- ----------------------------------------------------------------------------------------------------------
320+ QUERY PLAN
321+ ----------------------------------------------------------------------------------------------------
242322 Delete on tmp
243323 CTE q
244324 -> Delete on tmp t
245- -> Hash Join
246- Hash Cond: (t.id = r.id)
247- -> Seq Scan on tmp t
248- -> Hash
249- -> Append
250- -> Index Scan using range_rel_pkey on range_rel r
251- Filter: (dt = 'Sat Jan 02 00:00:00 2010'::timestamp without time zone)
252- -> Index Scan using range_rel_1_pkey on range_rel_1 r_1
253- Filter: (dt = 'Sat Jan 02 00:00:00 2010'::timestamp without time zone)
325+ -> Merge Join
326+ Merge Cond: (r.id = t.id)
327+ -> Merge Append
328+ Sort Key: r.id
329+ -> Index Scan using range_rel_pkey on range_rel r
330+ Filter: (dt = 'Sat Jan 02 00:00:00 2010'::timestamp without time zone)
331+ -> Index Scan using range_rel_1_pkey on range_rel_1 r_1
332+ Filter: (dt = 'Sat Jan 02 00:00:00 2010'::timestamp without time zone)
333+ -> Sort
334+ Sort Key: t.id
335+ -> Seq Scan on tmp t
254336 -> Nested Loop
337+ -> Seq Scan on tmp
255338 -> CTE Scan on q
256- -> Materialize
257- -> Seq Scan on tmp
258- (16 rows)
339+ (17 rows)
259340
260341BEGIN;
261342WITH q AS (DELETE FROM test.tmp t
@@ -265,6 +346,6 @@ WITH q AS (DELETE FROM test.tmp t
265346DELETE FROM test.tmp USING q;
266347ROLLBACK;
267348DROP SCHEMA test CASCADE;
268- NOTICE: drop cascades to 15 other objects
349+ NOTICE: drop cascades to 27 other objects
269350DROP EXTENSION pg_pathman CASCADE;
270351DROP SCHEMA pathman CASCADE;
0 commit comments