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);
20 create or replace function t_tt1_1() returns trigger as $$
26 create or replace function t_tt1_2() returns trigger as $$
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);
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);
45 \echo ###### Update, Trigger
46 explain (analyze on, buffers on, verbose on, format :format)
47 update tt1 set a = a + 1;
49 explain (analyze on, buffers on, verbose on, format :format)
50 delete from tt1 where a % 10 = 0;
54 insert into tt1 (select a from generate_series(0, 10000) a);
57 \echo ###### Result, Append Seq Scan
58 explain (analyze on, buffers on, verbose on, format :format)
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
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))
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;
94 set work_mem = '128kB';
95 explain (analyze on, buffers on, verbose on, format :format)
96 select b from tt1 group by b;
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;
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
151 reset enable_seqscan;
152 reset enable_indexscan;
153 reset enable_bitmapscan;
156 explain (analyze on, buffers on, verbose on, format :format)
157 select * from tt3 where ctid = '(0,28)';
158 \echo ###### LockRows
160 explain (analyze on, buffers on, verbose on, format :format)
161 select a from tt1 where a % 10 = 0 for update;
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), ());
182 -- BitmapAnd/Inner/Right/ForegnScan