makeplanfile.sql

   1 \set format json
   2 \pset pager
   3
   4 set work_mem = '1MB';
   5 drop table if exists tt1;
   6 drop table if exists tt2;
   7 drop table if exists tt3;
   8 drop table if exists p cascade;
   9 drop table if exists ct1;
  10 create table p (a int, b int, c text);
  11 create table tt1 (a int, b int not null, c text) inherits (p);
  12 create table tt2 (a int, b int, c text) inherits (p);
  13 create table tt3 (a int, b int, c text) inherits (p);
  14 create index i_tt1 on tt1(a);
  15 create index i_tt2 on tt2(a);
  16 create index i_tt3_a on tt3(a);
  17 create index i_tt3_b on tt3(b);
  18 create table ct1 (a int unique, b int);
  19 insert into ct1 values (1,1), (2,2);
  20
  21 create or replace function t_tt1_1() returns trigger as $$
  22   BEGIN
  23     NEW.b := -NEW.a;
  24     RETURN NEW;
  25   END;
  26 $$ language plpgsql;
  27 create or replace function t_tt1_2() returns trigger as $$
  28   BEGIN
  29     NEW.c := 'tt1';
  30     RETURN NEW;
  31   END;
  32 $$ language plpgsql;
  33 create trigger tt1_trig_1 before insert or update on tt1
  34   for each row execute procedure t_tt1_1();
  35 create trigger tt1_trig_2 before insert or update on tt1
  36   for each row execute procedure t_tt1_2();
  37 insert into tt2 (select a, -a, 'tt2' from generate_series(7000, 17000) a);
  38 insert into tt3 (select a, -a, 'tt3' from generate_series(0, 100000) a);
  39 insert into tt3 (select 5000,  a, 'tt3' from generate_series(0, 40000) a);
  40 insert into tt3 (select a,  555, 'tt3' from generate_series(0, 40000) a);
  41
  42 \echo ###### Insert, Trigger
  43 explain (analyze on, buffers on, verbose on, format :format)
  44    insert into tt1 (select a from generate_series(0, 10000) a);
  45
  46 \echo ###### Update, Trigger
  47 explain (analyze on, buffers on, verbose on, format :format)
  48    update tt1 set a = a + 1;
  49 \echo ###### Delete
  50 explain (analyze on, buffers on, verbose on, format :format)
  51    delete from tt1 where a % 10 = 0;
  52
  53 ----
  54 delete from tt1;
  55 insert into tt1 (select a from generate_series(0, 10000) a);
  56 analyze;
  57
  58 \echo ###### Result, Append Seq Scan
  59 explain (analyze on, buffers on, verbose on, format :format)
  60    select *, 1 from
  61    (select a + 1, 3 from tt1 union all select a, 4 from tt2) as x;
  62 \echo ###### Index scan (forward) ANY, array in expr, escape
  63 explain (analyze on, buffers on, verbose on, format :format)
  64    select * from tt1 "x""y" where a in (50, 120, 300, 500);
  65 \echo ###### Index scan (backward), MergeJoin, Sort, quicksort, alias
  66 explain (analyze on, buffers on, verbose on, format :format)
  67    select x.b, x.c  from tt1 x join tt2 y on (x.a = -y.b * 3)
  68    order by x.a desc limit 10;
  69 \echo ###### IndexOnlyScan
  70 explain (analyze on, buffers on, verbose on, format :format)
  71     select a from tt1 where a < 10;
  72 \echo ###### Plain Aggregate, CTE, Recursive Union, WorkTable Scan, CTE Scan
  73 explain (analyze on, buffers on, verbose on, format :format)
  74    with recursive cte1(a) as
  75       (select 1 union all
  76        select a + 1 from cte1 where a < 10)
  77    select sum(a) from cte1;
  78 \echo ###### FunctionScan, Hash/HashJoin, Nested Loop
  79 explain (analyze on, buffers on, verbose on, format :format)
  80    select datname from pg_stat_activity;
  81 \echo ###### MergeAppend, Values
  82 explain (analyze on, buffers on, verbose on, format :format)
  83    (select a from tt1 order by a) union all
  84    (select a from (values (100), (200), (300)) as tv(a))
  85     order by a;
  86 \echo ###### Append, HashAggregate
  87 explain (analyze on, buffers on, verbose on, format :format)
  88    select a from tt1 union select b from tt2;
  89 \echo ###### GroupAggregate
  90 set work_mem = '128kB';
  91 explain (analyze on, buffers on, verbose on, format :format)
  92    select sum(a) from tt1 group by b;
  93 set work_mem = '1MB';
  94 \echo ###### Group
  95 set work_mem = '128kB';
  96 explain (analyze on, buffers on, verbose on, format :format)
  97    select b from tt1 group by b;
  98 set work_mem = '1MB';
  99 \echo ###### SetOp intersect, SubqueryScan
 100 explain (analyze on, buffers on, verbose on, format :format)
 101    select a from tt1 intersect select b from tt2 order by a;
 102 \echo ###### Sorted SetOp, Sort on Disk
 103 set work_mem = '128kB';
 104 explain (analyze on, buffers on, verbose on, format :format)
 105    select a from tt1 intersect select b from tt2 order by a;
 106 set work_mem = '1MB';
 107 \echo ###### HashSetOp intersect All, SubqueryScan
 108 explain (analyze on, buffers on, verbose on, format :format)
 109    select a from tt1 intersect all select b from tt2 order by a;
 110 \echo ###### HashSetOp except, SubqueryScan
 111 explain (analyze on, buffers on, verbose on, format :format)
 112    select a from tt1 except select b from tt2 order by a;
 113 \echo ###### HashSetOp except all, SubqueryScan
 114 explain (analyze on, buffers on, verbose on, format :format)
 115    select a from tt1 except all select b from tt2 order by a;
 116 \echo ###### merge LEFT join
 117 set work_mem = '64kB';
 118 explain (analyze on, buffers on, verbose on, format :format)
 119    select x.b from tt1 x left join tt3 y on (x.a = y.a);
 120 set work_mem = '1MB';
 121 \echo ###### hash FULL join
 122 explain (analyze on, buffers on, verbose on, format :format)
 123    select x.b from tt1 x full outer join tt2 y on (x.a = y.a);
 124 \echo ###### hash SEMI join
 125 explain (analyze on, buffers on, verbose on, format :format)
 126    select * from tt1 where a = any(select b from tt2);
 127 \echo ###### Hash Anti Join
 128 explain (analyze on, buffers on, verbose on, format :format)
 129    select * from tt1 where not exists (select * from tt2 where tt1.a = tt2.b);
 130 \echo ###### WindowAgg
 131 explain (analyze on, buffers on, verbose on, format :format)
 132    select first_value(a) over (partition by a / 10) from tt1;
 133 \echo ###### Unique
 134 explain (analyze on, buffers on, verbose on, format :format)
 135    select distinct a from tt1 order by a;
 136 \echo ###### PlainAggregate
 137 explain (analyze on, buffers on, verbose on, format :format)
 138    select sum(a) from tt1;
 139 \echo ###### BitmapIndexScan/BitmapHeapScan, BitmapOr, lossy
 140 set enable_seqscan to false;
 141 set work_mem to '64kB';
 142 explain (analyze on, buffers on, verbose on, format :format)
 143    select * from tt3 where b > -99998;
 144 \echo ###### Join Filter
 145 set enable_seqscan to true;
 146 set enable_indexscan to false;
 147 set enable_bitmapscan to false;
 148 explain (analyze on, buffers on, verbose on, format :format)
 149    SELECT tt2.* from tt2
 150    LEFT OUTER JOIN tt3 ON (tt2.a < tt3.a) where tt3.a + tt2.a < 100000
 151    LIMIT 100;
 152 reset enable_seqscan;
 153 reset enable_indexscan;
 154 reset enable_bitmapscan;
 155 reset work_mem;
 156 \echo ###### TidScan
 157 explain (analyze on, buffers on, verbose on, format :format)
 158    select * from tt3 where ctid = '(0,28)';
 159 \echo ###### LockRows
 160 begin;
 161 explain (analyze on, buffers on, verbose on, format :format)
 162    select a from tt1 where a % 10 = 0 for update;
 163 rollback;
 164 \echo ###### Materialize
 165 explain (analyze on, buffers on, verbose on, format :format)
 166    select * from tt1 where a = all(select b from tt2);
 167 \echo ###### Update on partitioned tables
 168 explain (analyze on, buffers on, verbose on, format :format)
 169    UPDATE p SET b = b + 1;
 170 \echo ###### Delete on partitioned tables
 171 explain (analyze on, buffers on, verbose on, format :format)
 172    DELETE FROM p WHERE a = 100;
 173 \echo ###### ON CONFLICT
 174 explain (analyze on, buffers on, verbose on, format :format)
 175    INSERT INTO ct1 VALUES (1,1) ON CONFLICT (a) DO UPDATE SET b = EXCLUDED.b + 1;
 176 \echo ###### GROUP BY
 177 explain (analyze on, buffers on, verbose on, format :format)
 178    SELECT a, b, max(c) FROM tt1 GROUP BY a, b;
 179 \echo ###### GROUPING SETS
 180 explain (analyze on, buffers on, verbose on, format :format)
 181    SELECT a, b, max(c) FROM tt1 GROUP BY GROUPING SETS ((a), (b), ());
 182 \echo ###### Table sample
 183 explain (analyze on, buffers on, verbose on, format :format)
 184    SELECT * FROM tt1 TABLESAMPLE system(1) REPEATABLE (1);
 185
 186 \echo ###### Project Set
 187 explain (analyze on, buffers on, verbose on, format :format)
 188    SELECT * from XMLTABLE('//towns/town'
 189     PASSING BY REF '<towns><town><name>Toronto</name></town><town><name>Ottawa</name></town></towns>'
 190          COLUMNS name text);
 191
 192 \echo ###### Incremental Sort
 193 explain (analyze on, buffers on, verbose on, format :format)
 194         WITH x AS (SELECT i/100 + 1 AS a, i + 1 AS b FROM generate_series(0, 999) i)
 195           SELECT * FROM (SELECT * FROM x ORDER BY a) s ORDER BY a, b LIMIT 31;
 196
 197 -- Named Tuplestore Scan -- requires auto_explain
 198 DROP TABLE IF EXISTS e1 CASCADE;
 199 CREATE TABLE e1 (a int, b int);
 200 CREATE OR REPLACE function e1_t1() RETURNS TRIGGER AS $$
 201 DECLARE
 202   total int;
 203 BEGIN
 204   SELECT sum(a) INTO total FROM post;
 205   NEW.b := total;
 206   RETURN NEW;
 207 END;
 208 $$ LANGUAGE plpgsql;
 209 CREATE TRIGGER e1_t1 AFTER INSERT ON e1
 210  REFERENCING NEW TABLE AS post
 211  FOR EACH ROW EXECUTE PROCEDURE e1_t1();
 212
 213 INSERT INTO e1 VALUES (1, 1);
 214
 215 load 'auto_explain';
 216 set auto_explain.log_min_duration to 0;
 217 set auto_explain.log_analyze to true;
 218 set auto_explain.log_buffers to true;
 219 set auto_explain.log_buffers to true;
 220 set auto_explain.log_format to :format;
 221 set auto_explain.log_timing  to true;
 222 set auto_explain.log_nested_statements to true;
 223 set client_min_messages to LOG;
 224 set log_min_messages to FATAL; -- Inhibit LOG by auto_explain
 225 \echo ###### Named Tuplestore Scan
 226 CREATE TRIGGER e1_t2 AFTER UPDATE ON e1
 227  REFERENCING NEW TABLE AS post OLD TABLE AS pre
 228  FOR EACH ROW EXECUTE PROCEDURE e1_t1();
 229 UPDATE e1 SET a = a + 1;
 230 set client_min_messages to DEFAULT;
 231 set log_min_messages to DEFAULT;
 232 set auto_explain.log_min_duration to -1;
 233
 234 -- ###### Parallel
 235 drop table if exists lt1;
 236 create table lt1 (a int, b text);
 237 alter table lt1 alter column b set storage plain;
 238 insert into lt1 (select a, repeat('x', 1000) from generate_series(0, 99999) a);
 239 set max_parallel_workers_per_gather to 2;
 240 set parallel_tuple_cost to 0;
 241 set parallel_setup_cost to 0;
 242 set min_parallel_table_scan_size to 0;
 243 set min_parallel_index_scan_size to 0;
 244
 245 \echo ###### Parallel Seq Scan
 246 explain (analyze on, buffers on, verbose on, format :format)
 247    SELECT * FROM lt1;
 248
 249 \echo ###### Parallel Index Scan
 250 explain (analyze on, buffers on, verbose on, format :format)
 251    SELECT * FROM tt1 where a < 100;
 252
 253 \echo ###### Gather Merge
 254 explain (analyze on, buffers on, verbose on, format :format)
 255    SELECT a FROM tt1 ORDER BY a;
 256
 257 -- BitmapAnd/Inner/Right/ForegnScan