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 create table p (a int, b int, c text);
  10 create table tt1 (a int, b int not null, c text) inherits (p);
  11 create table tt2 (a int, b int, c text) inherits (p);
  12 create table tt3 (a int, b int, c text) inherits (p);
  13 create index i_tt1 on tt1(a);
  14 create index i_tt2 on tt2(a);
  15 create index i_tt3_a on tt3(a);
  16 create index i_tt3_b on tt3(b);
  17 create table ct1 (a int unique, b int);
  18 insert into ct1 values (1,1), (2,2);
  19
  20 create or replace function t_tt1_1() returns trigger as $$
  21   BEGIN
  22     NEW.b := -NEW.a;
  23     RETURN NEW;
  24   END;
  25 $$ language plpgsql;
  26 create or replace function t_tt1_2() returns trigger as $$
  27   BEGIN
  28     NEW.c := 'tt1';
  29     RETURN NEW;
  30   END;
  31 $$ language plpgsql;
  32 create trigger tt1_trig_1 before insert or update on tt1
  33   for each row execute procedure t_tt1_1();
  34 create trigger tt1_trig_2 before insert or update on tt1
  35   for each row execute procedure t_tt1_2();
  36 insert into tt2 (select a, -a, 'tt2' from generate_series(7000, 17000) a);
  37 insert into tt3 (select a, -a, 'tt3' from generate_series(0, 100000) a);
  38 insert into tt3 (select 5000,  a, 'tt3' from generate_series(0, 40000) a);
  39 insert into tt3 (select a,  555, 'tt3' from generate_series(0, 40000) a);
  40
  41 \echo ###### Insert, Trigger
  42 explain (analyze on, buffers on, verbose on, format :format)
  43    insert into tt1 (select a from generate_series(0, 10000) a);
  44
  45 \echo ###### Update, Trigger
  46 explain (analyze on, buffers on, verbose on, format :format)
  47    update tt1 set a = a + 1;
  48 \echo ###### Delete
  49 explain (analyze on, buffers on, verbose on, format :format)
  50    delete from tt1 where a % 10 = 0;
  51
  52 ----
  53 delete from tt1;
  54 insert into tt1 (select a from generate_series(0, 10000) a);
  55 analyze;
  56
  57 \echo ###### Result, Append Seq Scan
  58 explain (analyze on, buffers on, verbose on, format :format)
  59    select *, 1 from
  60    (select a + 1, 3 from tt1 union all select a, 4 from tt2) as x;
  61 \echo ###### Index scan (forward) ANY, array in expr, escape
  62 explain (analyze on, buffers on, verbose on, format :format)
  63    select * from tt1 "x""y" where a in (50, 120, 300, 500);
  64 \echo ###### Index scan (backward), MergeJoin, Sort, quicksort, alias
  65 explain (analyze on, buffers on, verbose on, format :format)
  66    select x.b, x.c  from tt1 x join tt2 y on (x.a = -y.b * 3)
  67    order by x.a desc limit 10;
  68 \echo ###### IndexOnlyScan
  69 explain (analyze on, buffers on, verbose on, format :format)
  70     select a from tt1 where a < 10;
  71 \echo ###### Plain Aggregate, CTE, Recursive Union, WorkTable Scan, CTE Scan
  72 explain (analyze on, buffers on, verbose on, format :format)
  73    with recursive cte1(a) as
  74       (select 1 union all
  75        select a + 1 from cte1 where a < 10)
  76    select sum(a) from cte1;
  77 \echo ###### FunctionScan, Hash/HashJoin, Nested Loop
  78 explain (analyze on, buffers on, verbose on, format :format)
  79    select datname from pg_stat_activity;
  80 \echo ###### MergeAppend, Values
  81 explain (analyze on, buffers on, verbose on, format :format)
  82    (select a from tt1 order by a) union all
  83    (select a from (values (100), (200), (300)) as tv(a))
  84     order by a;
  85 \echo ###### Append, HashAggregate
  86 explain (analyze on, buffers on, verbose on, format :format)
  87    select a from tt1 union select b from tt2;
  88 \echo ###### GroupAggregate
  89 set work_mem = '128kB';
  90 explain (analyze on, buffers on, verbose on, format :format)
  91    select sum(a) from tt1 group by b;
  92 set work_mem = '1MB';
  93 \echo ###### Group
  94 set work_mem = '128kB';
  95 explain (analyze on, buffers on, verbose on, format :format)
  96    select b from tt1 group by b;
  97 set work_mem = '1MB';
  98 \echo ###### SetOp intersect, SbuqueryScan
  99 explain (analyze on, buffers on, verbose on, format :format)
 100    select a from tt1 intersect select b from tt2 order by a;
 101 \echo ###### Sorted SetOp, Sort on Disk
 102 set work_mem = '128kB';
 103 explain (analyze on, buffers on, verbose on, format :format)
 104    select a from tt1 intersect select b from tt2 order by a;
 105 set work_mem = '1MB';
 106 \echo ###### HashSetOp intersect All, SubqueryScan
 107 explain (analyze on, buffers on, verbose on, format :format)
 108    select a from tt1 intersect all select b from tt2 order by a;
 109 \echo ###### HashSetOp except, SubqueryScan
 110 explain (analyze on, buffers on, verbose on, format :format)
 111    select a from tt1 except select b from tt2 order by a;
 112 \echo ###### HashSetOp except all, SubqueryScan
 113 explain (analyze on, buffers on, verbose on, format :format)
 114    select a from tt1 except all select b from tt2 order by a;
 115 \echo ###### merge LEFT join
 116 set work_mem = '64kB';
 117 explain (analyze on, buffers on, verbose on, format :format)
 118    select x.b from tt1 x left join tt3 y on (x.a = y.a);
 119 set work_mem = '1MB';
 120 \echo ###### hash FULL join
 121 explain (analyze on, buffers on, verbose on, format :format)
 122    select x.b from tt1 x full outer join tt2 y on (x.a = y.a);
 123 \echo ###### hash SEMI join
 124 explain (analyze on, buffers on, verbose on, format :format)
 125    select * from tt1 where a = any(select b from tt2);
 126 \echo ###### Hash Anti Join
 127 explain (analyze on, buffers on, verbose on, format :format)
 128    select * from tt1 where not exists (select * from tt2 where tt1.a = tt2.b);
 129 \echo ###### WindowAgg
 130 explain (analyze on, buffers on, verbose on, format :format)
 131    select first_value(a) over (partition by a / 10) from tt1;
 132 \echo ###### Unique
 133 explain (analyze on, buffers on, verbose on, format :format)
 134    select distinct a from tt1 order by a;
 135 \echo ###### PlainAggregate
 136 explain (analyze on, buffers on, verbose on, format :format)
 137    select sum(a) from tt1;
 138 \echo ###### BitmapIndexScan/BitmapHeapScan, BitmapOr, lossy
 139 set enable_seqscan to false;
 140 set work_mem to '64kB';
 141 explain (analyze on, buffers on, verbose on, format :format)
 142    select * from tt3 where b > -99998;
 143 \echo ###### Join Filter
 144 set enable_seqscan to true;
 145 set enable_indexscan to false;
 146 set enable_bitmapscan to false;
 147 explain (analyze on, buffers on, verbose on, format :format)
 148    SELECT tt2.* from tt2
 149    LEFT OUTER JOIN tt3 ON (tt2.a < tt3.a) where tt3.a + tt2.a < 100000
 150    LIMIT 100;
 151 reset enable_seqscan;
 152 reset enable_indexscan;
 153 reset enable_bitmapscan;
 154 reset work_mem;
 155 \echo ###### TidScan
 156 explain (analyze on, buffers on, verbose on, format :format)
 157    select * from tt3 where ctid = '(0,28)';
 158 \echo ###### LockRows
 159 begin;
 160 explain (analyze on, buffers on, verbose on, format :format)
 161    select a from tt1 where a % 10 = 0 for update;
 162 rollback;
 163 \echo ###### Materialize
 164 explain (analyze on, buffers on, verbose on, format :format)
 165    select * from tt1 where a = all(select b from tt2);
 166 \echo ###### Update on partitioned tables
 167 explain (analyze on, buffers on, verbose on, format :format)
 168    UPDATE p SET b = b + 1;
 169 \echo ###### Delete on partitioned tables
 170 explain (analyze on, buffers on, verbose on, format :format)
 171    DELETE FROM p WHERE a = 100;
 172 \echo ###### ON CONFLICT
 173 explain (analyze on, buffers on, verbose on, format :format)
 174    INSERT INTO ct1 VALUES (1,1) ON CONFLICT (a) DO UPDATE SET b = EXCLUDED.b + 1;
 175 \echo ###### GROUP BY
 176 explain (analyze on, buffers on, verbose on, format :format)
 177    SELECT a, b, max(c) FROM tt1 GROUP BY a, b;
 178 \echo ###### GROUPING SETS
 179 explain (analyze on, buffers on, verbose on, format :format)
 180    SELECT a, b, max(c) FROM tt1 GROUP BY GROUPING SETS ((a), (b), ());
 181 \echo ###### Table sample
 182 explain (analyze on, buffers on, verbose on, format :format)
 183    SELECT * FROM tt1 TABLESAMPLE system(1) REPEATABLE (1);
 184
 185 \echo ###### Parallel
 186 create table lt1 (a int, b text);
 187 alter table lt1 alter column b set storage plain;
 188 insert into lt1 (select a, repeat('x', 1000) from generate_series(0, 99999) a);
 189 set max_parallel_worders_per_gather to 2;
 190 set parallel_tuple_cost to 0;
 191 set parallel_setup_cost to 0;
 192 explain (analyze on, buffers on, verbose on, format :format)
 193    SELECT * FROM lt1;
 194
 195 -- BitmapAnd/Inner/Right/ForegnScan