· 7 years ago · Dec 25, 2018, 11:54 AM
1/** Be aware that the module / extension pgcrypto must be install in the path,
2 * e. g. create extension if not exists pgcrypto schema public;
3 */
4
5\echo Only output failed statements to the screen
6\set ECHO errors
7\echo Switch off autocommit
8\set AUTOCOMMIT off
9\echo Make the execution rollback and stop on error
10\set ON_ERROR_ROLLBACK On
11\set ON_ERROR_STOP On
12
13
14\echo
15\echo get DB details
16select version();;
17
18
19\echo
20\echo Clean up if necessary
21drop table if exists RESTRICTING_TABLE;
22
23drop table if exists SOURCE_TABLE;
24
25drop table if exists TO_BE_UPDATED;
26
27
28\echo
29\echo Prepare table structures
30create unlogged table TO_BE_UPDATED (
31 KEY_U bigint,
32 COL_1 uuid,
33 COL_2 numeric(10, 2)
34)
35;
36
37create unlogged table SOURCE_TABLE (
38 KEY_S bigint,
39 ATT_3 uuid
40)
41;
42
43create unlogged table RESTRICTING_TABLE (
44 KEY_R bigint,
45 SOME_CONDITION date,
46 ATT_4 numeric(10, 2)
47)
48;
49
50
51\echo
52\echo Fill tables
53with recursive GENERATOR (N, D) as (
54 /** It very much seems that the recursive call must come first. */
55 values(1, make_date(year => 1973, month => 4, day => 18))
56 union all
57 select N + 1,
58 D - 1
59 from GENERATOR
60 where N < 1000000
61)
62, seed as (
63 select setseed(0)
64)
65, INTO_U as (
66 insert into TO_BE_UPDATED (KEY_U, COL_1, COL_2)
67 select N,
68 gen_random_uuid(),
69 random() * power(10, 7)
70 from GENERATOR
71)
72, INTO_S as (
73 insert into SOURCE_TABLE (KEY_S, ATT_3)
74 select N,
75 gen_random_uuid()
76 from GENERATOR
77)
78insert into RESTRICTING_TABLE (KEY_R, SOME_CONDITION, ATT_4)
79 select N,
80 D,
81 - random() * power(10, 7)
82 from GENERATOR
83 where mod(N, 10000) = 0
84;
85
86commit;
87
88
89\echo
90\echo Create constraints
91alter table TO_BE_UPDATED
92 add primary key (KEY_U);
93
94alter table SOURCE_TABLE
95 add primary key (KEY_S),
96 add foreign key (KEY_S) references TO_BE_UPDATED (KEY_U);
97
98alter table RESTRICTING_TABLE
99 add primary key (KEY_R),
100 add foreign key (KEY_R) references TO_BE_UPDATED (KEY_U),
101 add foreign key (KEY_R) references SOURCE_TABLE (KEY_S);
102
103
104\echo
105\echo gather statistics
106analyze verbose TO_BE_UPDATED;
107analyze verbose SOURCE_TABLE;
108analyze verbose RESTRICTING_TABLE;
109
110
111\echo
112\echo Get explain plans
113-- \html
114\set ECHO all
115explain analyze verbose
116 update TO_BE_UPDATED U
117 set COL_1 = (
118 select S.ATT_3
119 from SOURCE_TABLE S
120 where S.KEY_S = U.KEY_U
121 ),
122 COL_2 = (
123 select R.ATT_4
124 from RESTRICTING_TABLE R
125 where R.SOME_CONDITION between make_date(year => 1066, month => 6, day => 6)
126 and make_date(year => 1901, month => 12, day => 24)
127 and R.KEY_R = U.KEY_U
128 )
129 where U.KEY_U in (
130 select R.KEY_R
131 from RESTRICTING_TABLE R
132 where R.SOME_CONDITION between make_date(year => 1066, month => 6, day => 6)
133 and make_date(year => 1901, month => 12, day => 24)
134 )
135;
136\set ECHO errors
137-- \html
138create table UPDATED_1 as
139 select * from TO_BE_UPDATED where COL_2 < 0;
140
141-- \html
142\set ECHO all
143explain analyze verbose
144 update TO_BE_UPDATED U
145 set COL_1 = (
146 select S.ATT_3
147 from SOURCE_TABLE S
148 inner join RESTRICTING_TABLE R
149 on S.KEY_S = R.KEY_R
150 where R.SOME_CONDITION between make_date(year => 1066, month => 6, day => 6)
151 and make_date(year => 1901, month => 12, day => 24)
152 and S.KEY_S = U.KEY_U
153 ),
154 COL_2 = (
155 select R.ATT_4
156 from SOURCE_TABLE S
157 inner join RESTRICTING_TABLE R
158 on S.KEY_S = R.KEY_R
159 where R.SOME_CONDITION between make_date(year => 1066, month => 6, day => 6)
160 and make_date(year => 1901, month => 12, day => 24)
161 and S.KEY_S = U.KEY_U
162 )
163 where exists (
164 select S.ATT_3, R.ATT_4
165 from SOURCE_TABLE S
166 inner join RESTRICTING_TABLE R
167 on S.KEY_S = R.KEY_R
168 where R.SOME_CONDITION between make_date(year => 1066, month => 6, day => 6)
169 and make_date(year => 1901, month => 12, day => 24)
170 and S.KEY_S = U.KEY_U
171 )
172;
173\set ECHO errors
174-- \html
175create table UPDATED_2 as
176 select * from TO_BE_UPDATED where COL_2 < 0;
177
178-- \html
179\set ECHO all
180explain analyze verbose
181 update TO_BE_UPDATED U
182 set (COL_1, COL_2) = (
183 select S.ATT_3, R.ATT_4
184 from SOURCE_TABLE S
185 inner join RESTRICTING_TABLE R
186 on S.KEY_S = R.KEY_R
187 where R.SOME_CONDITION between make_date(year => 1066, month => 6, day => 6)
188 and make_date(year => 1901, month => 12, day => 24)
189 and S.KEY_S = U.KEY_U
190 )
191 where exists (
192 select S.ATT_3, R.ATT_4
193 from SOURCE_TABLE S
194 inner join RESTRICTING_TABLE R
195 on S.KEY_S = R.KEY_R
196 where R.SOME_CONDITION between make_date(year => 1066, month => 6, day => 6)
197 and make_date(year => 1901, month => 12, day => 24)
198 and S.KEY_S = U.KEY_U
199 )
200;
201\set ECHO errors
202-- \html
203create table UPDATED_3 as
204 select * from TO_BE_UPDATED where COL_2 < 0;
205
206\echo
207\echo Regression 1 - 2
208WITH u2
209 AS (SELECT 'UPDATED_2' AS src, t.*
210 FROM updated_2 t
211 EXCEPT ALL
212 SELECT 'UPDATED_2' AS src, t.*
213 FROM updated_1 t)
214 , u1
215 AS (SELECT 'UPDATED_1' AS src, t.*
216 FROM updated_1 t
217 EXCEPT ALL
218 SELECT 'UPDATED_1' AS src, t.*
219 FROM updated_2 t)
220 , uni
221 AS (SELECT * FROM u1
222 UNION ALL
223 SELECT * FROM u2)
224 SELECT /*+ parallel(4) */
225 *
226 FROM uni
227ORDER BY 2 ASC, 1 ASC;
228
229\echo
230\echo Regression 1 - 3
231WITH u3
232 AS (SELECT 'UPDATED_3' AS src, t.*
233 FROM updated_3 t
234 EXCEPT ALL
235 SELECT 'UPDATED_3' AS src, t.*
236 FROM updated_1 t)
237 , u1
238 AS (SELECT 'UPDATED_1' AS src, t.*
239 FROM updated_1 t
240 EXCEPT ALL
241 SELECT 'UPDATED_1' AS src, t.*
242 FROM updated_3 t)
243 , uni
244 AS (SELECT * FROM u1
245 UNION ALL
246 SELECT * FROM u3)
247 SELECT /*+ parallel(4) */
248 *
249 FROM uni
250ORDER BY 2 ASC, 1 ASC;
251
252\echo Clean up
253drop table if exists RESTRICTING_TABLE;
254drop table if exists SOURCE_TABLE;
255drop table if exists TO_BE_UPDATED;
256
257
258/** The log is appended here */
259
260/** Be aware that the module / extension pgcrypto must be install in the path,
261
262 * e. g. create extension if not exists pgcrypto schema public;
263
264 * You also need to grant the execution to the user, you employ for this test,
265
266 * e. g. grant execution
267
268 */
269
270
271
272\echo Only output failed statements to the screen
273
274Only output failed statements to the screen
275\set ECHO errors
276
277Switch off autocommit
278Make the execution rollback and stop on error
279
280get DB details
281 version
282------------------------------------------------------------
283 PostgreSQL 10.4, compiled by Visual C++ build 1800, 32-bit
284(1 Zeile)
285
286
287Clean up if necessary
288DROP TABLE
289DROP TABLE
290DROP TABLE
291
292Prepare table structures
293CREATE TABLE
294CREATE TABLE
295CREATE TABLE
296
297Fill tables
298INSERT 0 100
299COMMIT
300
301Create constraints
302ALTER TABLE
303ALTER TABLE
304ALTER TABLE
305
306gather statistics
307psql:tmp_02.sql:108: INFO: analyzing "public.to_be_updated"
308psql:tmp_02.sql:108: INFO: "to_be_updated": scanned 8334 of 8334 pages, containing 1000000 live rows and 0 dead rows; 30000 rows in sample, 1000000 estimated total rows
309ANALYZE
310psql:tmp_02.sql:109: INFO: analyzing "public.source_table"
311psql:tmp_02.sql:109: INFO: "source_table": scanned 6370 of 6370 pages, containing 1000000 live rows and 0 dead rows; 30000 rows in sample, 1000000 estimated total rows
312ANALYZE
313psql:tmp_02.sql:110: INFO: analyzing "public.restricting_table"
314psql:tmp_02.sql:110: INFO: "restricting_table": scanned 1 of 1 pages, containing 100 live rows and 0 dead rows; 100 rows in sample, 100 estimated total rows
315ANALYZE
316
317Get explain plans
318explain analyze verbose
319
320 update TO_BE_UPDATED U
321
322 set COL_1 = (
323
324 select S.ATT_3
325
326 from SOURCE_TABLE S
327
328 where S.KEY_S = U.KEY_U
329
330 ),
331
332 COL_2 = (
333
334 select R.ATT_4
335
336 from RESTRICTING_TABLE R
337
338 where R.SOME_CONDITION between make_date(year => 1066, month => 6, day => 6)
339
340 and make_date(year => 1901, month => 12, day => 24)
341
342 and R.KEY_R = U.KEY_U
343
344 )
345
346 where U.KEY_U in (
347
348 select R.KEY_R
349
350 from RESTRICTING_TABLE R
351
352 where R.SOME_CONDITION between make_date(year => 1066, month => 6, day => 6)
353
354 and make_date(year => 1901, month => 12, day => 24)
355
356 )
357
358;
359
360 QUERY PLAN
361---------------------------------------------------------------------------------------------------------------------------------------------------------
362 Update on public.to_be_updated u (cost=0.42..611.19 rows=31 width=52) (actual time=2.217..2.217 rows=0 loops=1)
363 -> Nested Loop (cost=0.42..611.19 rows=31 width=52) (actual time=0.052..0.894 rows=31 loops=1)
364 Output: u.key_u, (SubPlan 1), (SubPlan 2), u.ctid, r.ctid
365 Inner Unique: true
366 -> Seq Scan on public.restricting_table r (cost=0.00..2.50 rows=31 width=14) (actual time=0.013..0.024 rows=31 loops=1)
367 Output: r.ctid, r.key_r
368 Filter: ((r.some_condition >= '1066-06-06'::date) AND (r.some_condition <= '1901-12-24'::date))
369 Rows Removed by Filter: 69
370 -> Index Scan using to_be_updated_pkey on public.to_be_updated u (cost=0.42..8.44 rows=1 width=14) (actual time=0.005..0.005 rows=1 loops=31)
371 Output: u.key_u, u.ctid
372 Index Cond: (u.key_u = r.key_r)
373 SubPlan 1
374 -> Index Scan using source_table_pkey on public.source_table s (cost=0.42..8.44 rows=1 width=16) (actual time=0.004..0.004 rows=1 loops=31)
375 Output: s.att_3
376 Index Cond: (s.key_s = u.key_u)
377 SubPlan 2
378 -> Seq Scan on public.restricting_table r_1 (cost=0.00..2.75 rows=1 width=9) (actual time=0.004..0.011 rows=1 loops=31)
379 Output: r_1.att_4
380 Filter: ((r_1.some_condition >= '1066-06-06'::date) AND (r_1.some_condition <= '1901-12-24'::date) AND (r_1.key_r = u.key_u))
381 Rows Removed by Filter: 99
382 Planning time: 0.631 ms
383 Execution time: 2.281 ms
384(22 Zeilen)
385
386\set ECHO errors
387
388SELECT 31
389explain analyze verbose
390
391 update TO_BE_UPDATED U
392
393 set COL_1 = (
394
395 select S.ATT_3
396
397 from SOURCE_TABLE S
398
399 inner join RESTRICTING_TABLE R
400
401 on S.KEY_S = R.KEY_R
402
403 where R.SOME_CONDITION between make_date(year => 1066, month => 6, day => 6)
404
405 and make_date(year => 1901, month => 12, day => 24)
406
407 and S.KEY_S = U.KEY_U
408
409 ),
410
411 COL_2 = (
412
413 select R.ATT_4
414
415 from SOURCE_TABLE S
416
417 inner join RESTRICTING_TABLE R
418
419 on S.KEY_S = R.KEY_R
420
421 where R.SOME_CONDITION between make_date(year => 1066, month => 6, day => 6)
422
423 and make_date(year => 1901, month => 12, day => 24)
424
425 and S.KEY_S = U.KEY_U
426
427 )
428
429 where exists (
430
431 select S.ATT_3, R.ATT_4
432
433 from SOURCE_TABLE S
434
435 inner join RESTRICTING_TABLE R
436
437 on S.KEY_S = R.KEY_R
438
439 where R.SOME_CONDITION between make_date(year => 1066, month => 6, day => 6)
440
441 and make_date(year => 1901, month => 12, day => 24)
442
443 and S.KEY_S = U.KEY_U
444
445 )
446
447;
448
449 QUERY PLAN
450---------------------------------------------------------------------------------------------------------------------------------------------------------------------
451 Update on public.to_be_updated u (cost=264.72..974.25 rows=31 width=58) (actual time=1.508..1.508 rows=0 loops=1)
452 -> Nested Loop (cost=264.72..974.25 rows=31 width=58) (actual time=0.289..1.442 rows=31 loops=1)
453 Output: u.key_u, (SubPlan 1), (SubPlan 2), u.ctid, s.ctid, r.ctid
454 Inner Unique: true
455 -> HashAggregate (cost=264.30..264.61 rows=31 width=28) (actual time=0.201..0.208 rows=31 loops=1)
456 Output: s.ctid, s.key_s, r.ctid, r.key_r
457 Group Key: s.key_s
458 -> Nested Loop (cost=0.42..264.22 rows=31 width=28) (actual time=0.048..0.181 rows=31 loops=1)
459 Output: s.ctid, s.key_s, r.ctid, r.key_r
460 Inner Unique: true
461 -> Seq Scan on public.restricting_table r (cost=0.00..2.50 rows=31 width=14) (actual time=0.017..0.030 rows=31 loops=1)
462 Output: r.ctid, r.key_r
463 Filter: ((r.some_condition >= '1066-06-06'::date) AND (r.some_condition <= '1901-12-24'::date))
464 Rows Removed by Filter: 69
465 -> Index Scan using source_table_pkey on public.source_table s (cost=0.42..8.44 rows=1 width=14) (actual time=0.004..0.004 rows=1 loops=31)
466 Output: s.ctid, s.key_s
467 Index Cond: (s.key_s = r.key_r)
468 -> Index Scan using to_be_updated_pkey on public.to_be_updated u (cost=0.42..0.49 rows=1 width=14) (actual time=0.003..0.003 rows=1 loops=31)
469 Output: u.key_u, u.ctid
470 Index Cond: (u.key_u = s.key_s)
471 SubPlan 1
472 -> Nested Loop (cost=0.42..11.20 rows=1 width=16) (actual time=0.012..0.018 rows=1 loops=31)
473 Output: s_1.att_3
474 -> Index Scan using source_table_pkey on public.source_table s_1 (cost=0.42..8.44 rows=1 width=24) (actual time=0.003..0.003 rows=1 loops=31)
475 Output: s_1.key_s, s_1.att_3
476 Index Cond: (s_1.key_s = u.key_u)
477 -> Seq Scan on public.restricting_table r_1 (cost=0.00..2.75 rows=1 width=8) (actual time=0.004..0.009 rows=1 loops=31)
478 Output: r_1.key_r, r_1.some_condition, r_1.att_4
479 Filter: ((r_1.some_condition >= '1066-06-06'::date) AND (r_1.some_condition <= '1901-12-24'::date) AND (r_1.key_r = u.key_u))
480 Rows Removed by Filter: 99
481 SubPlan 2
482 -> Nested Loop (cost=0.42..11.20 rows=1 width=9) (actual time=0.012..0.017 rows=1 loops=31)
483 Output: r_2.att_4
484 -> Index Only Scan using source_table_pkey on public.source_table s_2 (cost=0.42..8.44 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=31)
485 Output: s_2.key_s
486 Index Cond: (s_2.key_s = u.key_u)
487 Heap Fetches: 31
488 -> Seq Scan on public.restricting_table r_2 (cost=0.00..2.75 rows=1 width=17) (actual time=0.004..0.009 rows=1 loops=31)
489 Output: r_2.key_r, r_2.some_condition, r_2.att_4
490 Filter: ((r_2.some_condition >= '1066-06-06'::date) AND (r_2.some_condition <= '1901-12-24'::date) AND (r_2.key_r = u.key_u))
491 Rows Removed by Filter: 99
492 Planning time: 0.766 ms
493 Execution time: 1.645 ms
494(43 Zeilen)
495
496\set ECHO errors
497
498SELECT 31
499explain analyze verbose
500
501 update TO_BE_UPDATED U
502
503 set (COL_1, COL_2) = (
504
505 select S.ATT_3, R.ATT_4
506
507 from SOURCE_TABLE S
508
509 inner join RESTRICTING_TABLE R
510
511 on S.KEY_S = R.KEY_R
512
513 where R.SOME_CONDITION between make_date(year => 1066, month => 6, day => 6)
514
515 and make_date(year => 1901, month => 12, day => 24)
516
517 and S.KEY_S = U.KEY_U
518
519 )
520
521 where exists (
522
523 select S.ATT_3, R.ATT_4
524
525 from SOURCE_TABLE S
526
527 inner join RESTRICTING_TABLE R
528
529 on S.KEY_S = R.KEY_R
530
531 where R.SOME_CONDITION between make_date(year => 1066, month => 6, day => 6)
532
533 and make_date(year => 1901, month => 12, day => 24)
534
535 and S.KEY_S = U.KEY_U
536
537 )
538
539;
540
541 QUERY PLAN
542-------------------------------------------------------------------------------------------------------------------------------------------------------------------
543 Update on public.to_be_updated u (cost=264.72..626.97 rows=31 width=90) (actual time=1.117..1.117 rows=0 loops=1)
544 -> Nested Loop (cost=264.72..626.97 rows=31 width=90) (actual time=0.174..0.813 rows=31 loops=1)
545 Output: u.key_u, $1, $2, (SubPlan 1 (returns $1,$2)), u.ctid, s.ctid, r.ctid
546 Inner Unique: true
547 -> HashAggregate (cost=264.30..264.61 rows=31 width=28) (actual time=0.141..0.150 rows=31 loops=1)
548 Output: s.ctid, s.key_s, r.ctid, r.key_r
549 Group Key: s.key_s
550 -> Nested Loop (cost=0.42..264.22 rows=31 width=28) (actual time=0.021..0.130 rows=31 loops=1)
551 Output: s.ctid, s.key_s, r.ctid, r.key_r
552 Inner Unique: true
553 -> Seq Scan on public.restricting_table r (cost=0.00..2.50 rows=31 width=14) (actual time=0.014..0.023 rows=31 loops=1)
554 Output: r.ctid, r.key_r
555 Filter: ((r.some_condition >= '1066-06-06'::date) AND (r.some_condition <= '1901-12-24'::date))
556 Rows Removed by Filter: 69
557 -> Index Scan using source_table_pkey on public.source_table s (cost=0.42..8.44 rows=1 width=14) (actual time=0.003..0.003 rows=1 loops=31)
558 Output: s.ctid, s.key_s
559 Index Cond: (s.key_s = r.key_r)
560 -> Index Scan using to_be_updated_pkey on public.to_be_updated u (cost=0.42..0.49 rows=1 width=14) (actual time=0.003..0.003 rows=1 loops=31)
561 Output: u.key_u, u.ctid
562 Index Cond: (u.key_u = s.key_s)
563 SubPlan 1 (returns $1,$2)
564 -> Nested Loop (cost=0.42..11.20 rows=1 width=25) (actual time=0.012..0.017 rows=1 loops=31)
565 Output: s_1.att_3, r_1.att_4
566 -> Index Scan using source_table_pkey on public.source_table s_1 (cost=0.42..8.44 rows=1 width=24) (actual time=0.002..0.002 rows=1 loops=31)
567 Output: s_1.key_s, s_1.att_3
568 Index Cond: (s_1.key_s = u.key_u)
569 -> Seq Scan on public.restricting_table r_1 (cost=0.00..2.75 rows=1 width=17) (actual time=0.004..0.009 rows=1 loops=31)
570 Output: r_1.key_r, r_1.some_condition, r_1.att_4
571 Filter: ((r_1.some_condition >= '1066-06-06'::date) AND (r_1.some_condition <= '1901-12-24'::date) AND (r_1.key_r = u.key_u))
572 Rows Removed by Filter: 99
573 Planning time: 0.423 ms
574 Execution time: 1.170 ms
575(32 Zeilen)
576
577\set ECHO errors
578
579SELECT 31
580
581Regression 1 - 2
582 src | key_u | col_1 | col_2
583-----+-------+-------+-------
584(0 Zeilen)
585
586
587Regression 1 - 3
588 src | key_u | col_1 | col_2
589-----+-------+-------+-------
590(0 Zeilen)
591
592Clean up
593DROP TABLE
594DROP TABLE
595DROP TABLE