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);
21 create or replace function t_tt1_1() returns trigger as $$
27 create or replace function t_tt1_2() returns trigger as $$
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);
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);
46 \echo ###### Update, Trigger
47 explain (analyze on, buffers on, verbose on, format :format)
48 update tt1 set a = a + 1;
50 explain (analyze on, buffers on, verbose on, format :format)
51 delete from tt1 where a % 10 = 0;
55 insert into tt1 (select a from generate_series(0, 10000) a);
58 \echo ###### Result, Append Seq Scan
59 explain (analyze on, buffers on, verbose on, format :format)
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
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))
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;
95 set work_mem = '128kB';
96 explain (analyze on, buffers on, verbose on, format :format)
97 select b from tt1 group by b;
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;
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
152 reset enable_seqscan;
153 reset enable_indexscan;
154 reset enable_bitmapscan;
157 explain (analyze on, buffers on, verbose on, format :format)
158 select * from tt3 where ctid = '(0,28)';
159 \echo ###### LockRows
161 explain (analyze on, buffers on, verbose on, format :format)
162 select a from tt1 where a % 10 = 0 for update;
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);
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>'
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;
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 $$
204 SELECT sum(a) INTO total FROM post;
209 CREATE TRIGGER e1_t1 AFTER INSERT ON e1
210 REFERENCING NEW TABLE AS post
211 FOR EACH ROW EXECUTE PROCEDURE e1_t1();
213 INSERT INTO e1 VALUES (1, 1);
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;
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;
245 \echo ###### Parallel Seq Scan
246 explain (analyze on, buffers on, verbose on, format :format)
249 \echo ###### Parallel Index Scan
250 explain (analyze on, buffers on, verbose on, format :format)
251 SELECT * FROM tt1 where a < 100;
253 \echo ###### Gather Merge
254 explain (analyze on, buffers on, verbose on, format :format)
255 SELECT a FROM tt1 ORDER BY a;
257 -- BitmapAnd/Inner/Right/ForegnScan