Update with correlated subqueries test case for PostgreSQL

/** Be aware that the module / extension pgcrypto must be install in the path,
  * e. g. create extension if not exists pgcrypto schema public;
  */

\echo Only output failed statements to the screen
\set ECHO errors
\echo Switch off autocommit
\set AUTOCOMMIT off
\echo Make the execution rollback and stop on error
\set ON_ERROR_ROLLBACK On
\set ON_ERROR_STOP On


\echo
\echo get DB details
select version();;


\echo
\echo Clean up if necessary
drop table if exists RESTRICTING_TABLE;

drop table if exists SOURCE_TABLE;

drop table if exists TO_BE_UPDATED;


\echo
\echo Prepare table structures
create unlogged table TO_BE_UPDATED (
    KEY_U                       bigint,
    COL_1                       uuid,
    COL_2                       numeric(10, 2)
)
;

create unlogged table SOURCE_TABLE (
    KEY_S                       bigint,
    ATT_3                       uuid
)
;

create unlogged table RESTRICTING_TABLE  (
    KEY_R                       bigint,
    SOME_CONDITION              date,
    ATT_4                       numeric(10, 2)
)
;


\echo
\echo Fill tables
with recursive GENERATOR (N, D) as (
    /** It very much seems that the recursive call must come first. */
    values(1, make_date(year => 1973, month => 4, day => 18))
    union all
    select N + 1,
           D - 1
      from GENERATOR
    where N < 1000000
)
, seed as (
    select setseed(0)
)
, INTO_U as (
    insert into TO_BE_UPDATED (KEY_U, COL_1, COL_2)
        select N,
               gen_random_uuid(),
               random() * power(10, 7)
          from GENERATOR
)
, INTO_S as (
    insert into SOURCE_TABLE (KEY_S, ATT_3)
        select N,
               gen_random_uuid()
          from GENERATOR
)
insert into RESTRICTING_TABLE (KEY_R, SOME_CONDITION, ATT_4)
    select N,
           D,
           - random() * power(10, 7)
      from GENERATOR
     where mod(N, 10000) = 0
;

commit;


\echo
\echo Create constraints
alter table TO_BE_UPDATED
  add primary key (KEY_U);

alter table SOURCE_TABLE
  add primary key (KEY_S),
  add foreign key (KEY_S) references TO_BE_UPDATED (KEY_U);

alter table RESTRICTING_TABLE
  add primary key (KEY_R),
  add foreign key (KEY_R) references TO_BE_UPDATED (KEY_U),
  add foreign key (KEY_R) references SOURCE_TABLE (KEY_S);


\echo
\echo gather statistics
analyze verbose TO_BE_UPDATED;
analyze verbose SOURCE_TABLE;
analyze verbose RESTRICTING_TABLE;


\echo
\echo Get explain plans
-- \html
\set ECHO all
explain analyze verbose
    update TO_BE_UPDATED U
      set COL_1 = (
             select S.ATT_3
               from SOURCE_TABLE S
              where S.KEY_S = U.KEY_U
          ),
          COL_2 = (
             select R.ATT_4
               from RESTRICTING_TABLE R
              where R.SOME_CONDITION between make_date(year => 1066, month => 6, day => 6)
                                         and make_date(year => 1901, month => 12, day => 24)
                and R.KEY_R = U.KEY_U
          )
    where U.KEY_U in (
             select R.KEY_R
               from RESTRICTING_TABLE R
              where R.SOME_CONDITION between make_date(year => 1066, month => 6, day => 6)
                                         and make_date(year => 1901, month => 12, day => 24)
        )
;
\set ECHO errors
-- \html
create table UPDATED_1 as
    select * from TO_BE_UPDATED where COL_2 < 0;

-- \html
\set ECHO all
explain analyze verbose
    update TO_BE_UPDATED U
      set COL_1 = (
             select S.ATT_3
               from SOURCE_TABLE S
              inner join RESTRICTING_TABLE R
                 on S.KEY_S = R.KEY_R
              where R.SOME_CONDITION between make_date(year => 1066, month => 6, day => 6)
                                         and make_date(year => 1901, month => 12, day => 24)
                and S.KEY_S = U.KEY_U
          ),
          COL_2 = (
             select R.ATT_4
               from SOURCE_TABLE S
              inner join RESTRICTING_TABLE R
                 on S.KEY_S = R.KEY_R
              where R.SOME_CONDITION between make_date(year => 1066, month => 6, day => 6)
                                         and make_date(year => 1901, month => 12, day => 24)
                and S.KEY_S = U.KEY_U
          )
    where exists (
             select S.ATT_3, R.ATT_4
               from SOURCE_TABLE S
              inner join RESTRICTING_TABLE R
                 on S.KEY_S = R.KEY_R
              where R.SOME_CONDITION between make_date(year => 1066, month => 6, day => 6)
                                         and make_date(year => 1901, month => 12, day => 24)
                and S.KEY_S = U.KEY_U
        )
;
\set ECHO errors
-- \html
create table UPDATED_2 as
    select * from TO_BE_UPDATED where COL_2 < 0;

-- \html
\set ECHO all
explain analyze verbose
    update TO_BE_UPDATED U
      set (COL_1, COL_2) = (
             select S.ATT_3, R.ATT_4
               from SOURCE_TABLE S
              inner join RESTRICTING_TABLE R
                 on S.KEY_S = R.KEY_R
              where R.SOME_CONDITION between make_date(year => 1066, month => 6, day => 6)
                                         and make_date(year => 1901, month => 12, day => 24)
                and S.KEY_S = U.KEY_U
          )
    where exists (
             select S.ATT_3, R.ATT_4
               from SOURCE_TABLE S
              inner join RESTRICTING_TABLE R
                 on S.KEY_S = R.KEY_R
              where R.SOME_CONDITION between make_date(year => 1066, month => 6, day => 6)
                                         and make_date(year => 1901, month => 12, day => 24)
                and S.KEY_S = U.KEY_U
        )
;
\set ECHO errors
-- \html
create table UPDATED_3 as
    select * from TO_BE_UPDATED where COL_2 < 0;

\echo
\echo Regression 1 - 2
WITH u2
     AS (SELECT 'UPDATED_2' AS src, t.*
           FROM updated_2 t
         EXCEPT ALL
         SELECT 'UPDATED_2' AS src, t.*
           FROM updated_1 t)
   , u1
     AS (SELECT 'UPDATED_1' AS src, t.*
           FROM updated_1 t
         EXCEPT ALL
         SELECT 'UPDATED_1' AS src, t.*
           FROM updated_2 t)
   , uni
     AS (SELECT * FROM u1
         UNION ALL
         SELECT * FROM u2)
  SELECT /*+ parallel(4) */
        *
    FROM uni
ORDER BY 2 ASC, 1 ASC;

\echo
\echo Regression 1 - 3
WITH u3
     AS (SELECT 'UPDATED_3' AS src, t.*
           FROM updated_3 t
         EXCEPT ALL
         SELECT 'UPDATED_3' AS src, t.*
           FROM updated_1 t)
   , u1
     AS (SELECT 'UPDATED_1' AS src, t.*
           FROM updated_1 t
         EXCEPT ALL
         SELECT 'UPDATED_1' AS src, t.*
           FROM updated_3 t)
   , uni
     AS (SELECT * FROM u1
         UNION ALL
         SELECT * FROM u3)
  SELECT /*+ parallel(4) */
        *
    FROM uni
ORDER BY 2 ASC, 1 ASC;

\echo Clean up
drop table if exists RESTRICTING_TABLE;
drop table if exists SOURCE_TABLE;
drop table if exists TO_BE_UPDATED;


/** The log is appended here */

/** Be aware that the module / extension pgcrypto must be install in the path,

  * e. g. create extension if not exists pgcrypto schema public;

  * You also need to grant the execution to the user, you employ for this test,

  * e. g. grant execution

  */



\echo Only output failed statements to the screen

Only output failed statements to the screen
\set ECHO errors

Switch off autocommit
Make the execution rollback and stop on error

get DB details
                          version
------------------------------------------------------------
 PostgreSQL 10.4, compiled by Visual C++ build 1800, 32-bit
(1 Zeile)


Clean up if necessary
DROP TABLE
DROP TABLE
DROP TABLE

Prepare table structures
CREATE TABLE
CREATE TABLE
CREATE TABLE

Fill tables
INSERT 0 100
COMMIT

Create constraints
ALTER TABLE
ALTER TABLE
ALTER TABLE

gather statistics
psql:tmp_02.sql:108: INFO:  analyzing "public.to_be_updated"
psql: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
ANALYZE
psql:tmp_02.sql:109: INFO:  analyzing "public.source_table"
psql: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
ANALYZE
psql:tmp_02.sql:110: INFO:  analyzing "public.restricting_table"
psql: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
ANALYZE

Get explain plans
explain analyze verbose

    update TO_BE_UPDATED U

      set COL_1 = (

             select S.ATT_3

               from SOURCE_TABLE S

              where S.KEY_S = U.KEY_U

          ),

          COL_2 = (

             select R.ATT_4

               from RESTRICTING_TABLE R

              where R.SOME_CONDITION between make_date(year => 1066, month => 6, day => 6)

                                         and make_date(year => 1901, month => 12, day => 24)

                and R.KEY_R = U.KEY_U

          )

    where U.KEY_U in (

             select R.KEY_R

               from RESTRICTING_TABLE R

              where R.SOME_CONDITION between make_date(year => 1066, month => 6, day => 6)

                                         and make_date(year => 1901, month => 12, day => 24)

        )

;

                                                                       QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------
 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)
   ->  Nested Loop  (cost=0.42..611.19 rows=31 width=52) (actual time=0.052..0.894 rows=31 loops=1)
         Output: u.key_u, (SubPlan 1), (SubPlan 2), u.ctid, r.ctid
         Inner Unique: true
         ->  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)
               Output: r.ctid, r.key_r
               Filter: ((r.some_condition >= '1066-06-06'::date) AND (r.some_condition <= '1901-12-24'::date))
               Rows Removed by Filter: 69
         ->  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)
               Output: u.key_u, u.ctid
               Index Cond: (u.key_u = r.key_r)
         SubPlan 1
           ->  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)
                 Output: s.att_3
                 Index Cond: (s.key_s = u.key_u)
         SubPlan 2
           ->  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)
                 Output: r_1.att_4
                 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))
                 Rows Removed by Filter: 99
 Planning time: 0.631 ms
 Execution time: 2.281 ms
(22 Zeilen)

\set ECHO errors

SELECT 31
explain analyze verbose

    update TO_BE_UPDATED U

      set COL_1 = (

             select S.ATT_3

               from SOURCE_TABLE S

              inner join RESTRICTING_TABLE R

                 on S.KEY_S = R.KEY_R

              where R.SOME_CONDITION between make_date(year => 1066, month => 6, day => 6)

                                         and make_date(year => 1901, month => 12, day => 24)

                and S.KEY_S = U.KEY_U

          ),

          COL_2 = (

             select R.ATT_4

               from SOURCE_TABLE S

              inner join RESTRICTING_TABLE R

                 on S.KEY_S = R.KEY_R

              where R.SOME_CONDITION between make_date(year => 1066, month => 6, day => 6)

                                         and make_date(year => 1901, month => 12, day => 24)

                and S.KEY_S = U.KEY_U

          )

    where exists (

             select S.ATT_3, R.ATT_4

               from SOURCE_TABLE S

              inner join RESTRICTING_TABLE R

                 on S.KEY_S = R.KEY_R

              where R.SOME_CONDITION between make_date(year => 1066, month => 6, day => 6)

                                         and make_date(year => 1901, month => 12, day => 24)

                and S.KEY_S = U.KEY_U

        )

;

                                                                             QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------
 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)
   ->  Nested Loop  (cost=264.72..974.25 rows=31 width=58) (actual time=0.289..1.442 rows=31 loops=1)
         Output: u.key_u, (SubPlan 1), (SubPlan 2), u.ctid, s.ctid, r.ctid
         Inner Unique: true
         ->  HashAggregate  (cost=264.30..264.61 rows=31 width=28) (actual time=0.201..0.208 rows=31 loops=1)
               Output: s.ctid, s.key_s, r.ctid, r.key_r
               Group Key: s.key_s
               ->  Nested Loop  (cost=0.42..264.22 rows=31 width=28) (actual time=0.048..0.181 rows=31 loops=1)
                     Output: s.ctid, s.key_s, r.ctid, r.key_r
                     Inner Unique: true
                     ->  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)
                           Output: r.ctid, r.key_r
                           Filter: ((r.some_condition >= '1066-06-06'::date) AND (r.some_condition <= '1901-12-24'::date))
                           Rows Removed by Filter: 69
                     ->  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)
                           Output: s.ctid, s.key_s
                           Index Cond: (s.key_s = r.key_r)
         ->  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)
               Output: u.key_u, u.ctid
               Index Cond: (u.key_u = s.key_s)
         SubPlan 1
           ->  Nested Loop  (cost=0.42..11.20 rows=1 width=16) (actual time=0.012..0.018 rows=1 loops=31)
                 Output: s_1.att_3
                 ->  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)
                       Output: s_1.key_s, s_1.att_3
                       Index Cond: (s_1.key_s = u.key_u)
                 ->  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)
                       Output: r_1.key_r, r_1.some_condition, r_1.att_4
                       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))
                       Rows Removed by Filter: 99
         SubPlan 2
           ->  Nested Loop  (cost=0.42..11.20 rows=1 width=9) (actual time=0.012..0.017 rows=1 loops=31)
                 Output: r_2.att_4
                 ->  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)
                       Output: s_2.key_s
                       Index Cond: (s_2.key_s = u.key_u)
                       Heap Fetches: 31
                 ->  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)
                       Output: r_2.key_r, r_2.some_condition, r_2.att_4
                       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))
                       Rows Removed by Filter: 99
 Planning time: 0.766 ms
 Execution time: 1.645 ms
(43 Zeilen)

\set ECHO errors

SELECT 31
explain analyze verbose

    update TO_BE_UPDATED U

      set (COL_1, COL_2) = (

             select S.ATT_3, R.ATT_4

               from SOURCE_TABLE S

              inner join RESTRICTING_TABLE R

                 on S.KEY_S = R.KEY_R

              where R.SOME_CONDITION between make_date(year => 1066, month => 6, day => 6)

                                         and make_date(year => 1901, month => 12, day => 24)

                and S.KEY_S = U.KEY_U

          )

    where exists (

             select S.ATT_3, R.ATT_4

               from SOURCE_TABLE S

              inner join RESTRICTING_TABLE R

                 on S.KEY_S = R.KEY_R

              where R.SOME_CONDITION between make_date(year => 1066, month => 6, day => 6)

                                         and make_date(year => 1901, month => 12, day => 24)

                and S.KEY_S = U.KEY_U

        )

;

                                                                            QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
 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)
   ->  Nested Loop  (cost=264.72..626.97 rows=31 width=90) (actual time=0.174..0.813 rows=31 loops=1)
         Output: u.key_u, $1, $2, (SubPlan 1 (returns $1,$2)), u.ctid, s.ctid, r.ctid
         Inner Unique: true
         ->  HashAggregate  (cost=264.30..264.61 rows=31 width=28) (actual time=0.141..0.150 rows=31 loops=1)
               Output: s.ctid, s.key_s, r.ctid, r.key_r
               Group Key: s.key_s
               ->  Nested Loop  (cost=0.42..264.22 rows=31 width=28) (actual time=0.021..0.130 rows=31 loops=1)
                     Output: s.ctid, s.key_s, r.ctid, r.key_r
                     Inner Unique: true
                     ->  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)
                           Output: r.ctid, r.key_r
                           Filter: ((r.some_condition >= '1066-06-06'::date) AND (r.some_condition <= '1901-12-24'::date))
                           Rows Removed by Filter: 69
                     ->  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)
                           Output: s.ctid, s.key_s
                           Index Cond: (s.key_s = r.key_r)
         ->  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)
               Output: u.key_u, u.ctid
               Index Cond: (u.key_u = s.key_s)
         SubPlan 1 (returns $1,$2)
           ->  Nested Loop  (cost=0.42..11.20 rows=1 width=25) (actual time=0.012..0.017 rows=1 loops=31)
                 Output: s_1.att_3, r_1.att_4
                 ->  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)
                       Output: s_1.key_s, s_1.att_3
                       Index Cond: (s_1.key_s = u.key_u)
                 ->  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)
                       Output: r_1.key_r, r_1.some_condition, r_1.att_4
                       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))
                       Rows Removed by Filter: 99
 Planning time: 0.423 ms
 Execution time: 1.170 ms
(32 Zeilen)

\set ECHO errors

SELECT 31

Regression 1 - 2
 src | key_u | col_1 | col_2
-----+-------+-------+-------
(0 Zeilen)


Regression 1 - 3
 src | key_u | col_1 | col_2
-----+-------+-------+-------
(0 Zeilen)

Clean up
DROP TABLE
DROP TABLE
DROP TABLE