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