plpgsql: pure parser and reentrant scanner
[pgsql.git] / src / test / regress / sql / explain.sql
blob0bafa8704960e5eb8eb76978b72c5f9a4d8fae80
1 --
2 -- EXPLAIN
3 --
4 -- There are many test cases elsewhere that use EXPLAIN as a vehicle for
5 -- checking something else (usually planner behavior).  This file is
6 -- concerned with testing EXPLAIN in its own right.
7 --
9 -- To produce stable regression test output, it's usually necessary to
10 -- ignore details such as exact costs or row counts.  These filter
11 -- functions replace changeable output details with fixed strings.
13 create function explain_filter(text) returns setof text
14 language plpgsql as
16 declare
17     ln text;
18 begin
19     for ln in execute $1
20     loop
21         -- Replace any numeric word with just 'N'
22         ln := regexp_replace(ln, '-?\m\d+\M', 'N', 'g');
23         -- In sort output, the above won't match units-suffixed numbers
24         ln := regexp_replace(ln, '\m\d+kB', 'NkB', 'g');
25         -- Ignore text-mode buffers output because it varies depending
26         -- on the system state
27         CONTINUE WHEN (ln ~ ' +Buffers: .*');
28         -- Ignore text-mode "Planning:" line because whether it's output
29         -- varies depending on the system state
30         CONTINUE WHEN (ln = 'Planning:');
31         return next ln;
32     end loop;
33 end;
34 $$;
36 -- To produce valid JSON output, replace numbers with "0" or "0.0" not "N"
37 create function explain_filter_to_json(text) returns jsonb
38 language plpgsql as
40 declare
41     data text := '';
42     ln text;
43 begin
44     for ln in execute $1
45     loop
46         -- Replace any numeric word with just '0'
47         ln := regexp_replace(ln, '\m\d+\M', '0', 'g');
48         data := data || ln;
49     end loop;
50     return data::jsonb;
51 end;
52 $$;
54 -- Disable JIT, or we'll get different output on machines where that's been
55 -- forced on
56 set jit = off;
58 -- Similarly, disable track_io_timing, to avoid output differences when
59 -- enabled.
60 set track_io_timing = off;
62 -- Simple cases
64 select explain_filter('explain select * from int8_tbl i8');
65 select explain_filter('explain (analyze, buffers off) select * from int8_tbl i8');
66 select explain_filter('explain (analyze, buffers off, verbose) select * from int8_tbl i8');
67 select explain_filter('explain (analyze, buffers, format text) select * from int8_tbl i8');
68 select explain_filter('explain (analyze, buffers, format xml) select * from int8_tbl i8');
69 select explain_filter('explain (analyze, serialize, buffers, format yaml) select * from int8_tbl i8');
70 select explain_filter('explain (buffers, format text) select * from int8_tbl i8');
71 select explain_filter('explain (buffers, format json) select * from int8_tbl i8');
73 -- Check output including I/O timings.  These fields are conditional
74 -- but always set in JSON format, so check them only in this case.
75 set track_io_timing = on;
76 select explain_filter('explain (analyze, buffers, format json) select * from int8_tbl i8');
77 set track_io_timing = off;
79 -- SETTINGS option
80 -- We have to ignore other settings that might be imposed by the environment,
81 -- so printing the whole Settings field unfortunately won't do.
83 begin;
84 set local plan_cache_mode = force_generic_plan;
85 select true as "OK"
86   from explain_filter('explain (settings) select * from int8_tbl i8') ln
87   where ln ~ '^ *Settings: .*plan_cache_mode = ''force_generic_plan''';
88 select explain_filter_to_json('explain (settings, format json) select * from int8_tbl i8') #> '{0,Settings,plan_cache_mode}';
89 rollback;
91 -- GENERIC_PLAN option
93 select explain_filter('explain (generic_plan) select unique1 from tenk1 where thousand = $1');
94 -- should fail
95 select explain_filter('explain (analyze, generic_plan) select unique1 from tenk1 where thousand = $1');
97 -- MEMORY option
98 select explain_filter('explain (memory) select * from int8_tbl i8');
99 select explain_filter('explain (memory, analyze, buffers off) select * from int8_tbl i8');
100 select explain_filter('explain (memory, summary, format yaml) select * from int8_tbl i8');
101 select explain_filter('explain (memory, analyze, format json) select * from int8_tbl i8');
102 prepare int8_query as select * from int8_tbl i8;
103 select explain_filter('explain (memory) execute int8_query');
105 -- Test EXPLAIN (GENERIC_PLAN) with partition pruning
106 -- partitions should be pruned at plan time, based on constants,
107 -- but there should be no pruning based on parameter placeholders
108 create table gen_part (
109   key1 integer not null,
110   key2 integer not null
111 ) partition by list (key1);
112 create table gen_part_1
113   partition of gen_part for values in (1)
114   partition by range (key2);
115 create table gen_part_1_1
116   partition of gen_part_1 for values from (1) to (2);
117 create table gen_part_1_2
118   partition of gen_part_1 for values from (2) to (3);
119 create table gen_part_2
120   partition of gen_part for values in (2);
121 -- should scan gen_part_1_1 and gen_part_1_2, but not gen_part_2
122 select explain_filter('explain (generic_plan) select key1, key2 from gen_part where key1 = 1 and key2 = $1');
123 drop table gen_part;
126 -- Test production of per-worker data
128 -- Unfortunately, because we don't know how many worker processes we'll
129 -- actually get (maybe none at all), we can't examine the "Workers" output
130 -- in any detail.  We can check that it parses correctly as JSON, and then
131 -- remove it from the displayed results.
133 begin;
134 -- encourage use of parallel plans
135 set parallel_setup_cost=0;
136 set parallel_tuple_cost=0;
137 set min_parallel_table_scan_size=0;
138 set max_parallel_workers_per_gather=4;
140 select jsonb_pretty(
141   explain_filter_to_json('explain (analyze, verbose, buffers, format json)
142                          select * from tenk1 order by tenthous')
143   -- remove "Workers" node of the Seq Scan plan node
144   #- '{0,Plan,Plans,0,Plans,0,Workers}'
145   -- remove "Workers" node of the Sort plan node
146   #- '{0,Plan,Plans,0,Workers}'
147   -- Also remove its sort-type fields, as those aren't 100% stable
148   #- '{0,Plan,Plans,0,Sort Method}'
149   #- '{0,Plan,Plans,0,Sort Space Type}'
152 rollback;
154 -- Test display of temporary objects
155 create temp table t1(f1 float8);
157 create function pg_temp.mysin(float8) returns float8 language plpgsql
158 as 'begin return sin($1); end';
160 select explain_filter('explain (verbose) select * from t1 where pg_temp.mysin(f1) < 0.5');
162 -- Test compute_query_id
163 set compute_query_id = on;
164 select explain_filter('explain (verbose) select * from int8_tbl i8');
166 -- Test compute_query_id with utility statements containing plannable query
167 select explain_filter('explain (verbose) declare test_cur cursor for select * from int8_tbl');
168 select explain_filter('explain (verbose) create table test_ctas as select 1');
170 -- Test SERIALIZE option
171 select explain_filter('explain (analyze,buffers off,serialize) select * from int8_tbl i8');
172 select explain_filter('explain (analyze,serialize text,buffers,timing off) select * from int8_tbl i8');
173 select explain_filter('explain (analyze,serialize binary,buffers,timing) select * from int8_tbl i8');
174 -- this tests an edge case where we have no data to return
175 select explain_filter('explain (analyze,buffers off,serialize) create temp table explain_temp as select * from int8_tbl i8');
177 -- Test tuplestore storage usage in Window aggregate (memory case)
178 select explain_filter('explain (analyze,buffers off,costs off) select sum(n) over() from generate_series(1,10) a(n)');
179 -- Test tuplestore storage usage in Window aggregate (disk case)
180 set work_mem to 64;
181 select explain_filter('explain (analyze,buffers off,costs off) select sum(n) over() from generate_series(1,2000) a(n)');
182 -- Test tuplestore storage usage in Window aggregate (memory and disk case, final result is disk)
183 select explain_filter('explain (analyze,buffers off,costs off) select sum(n) over(partition by m) from (SELECT n < 3 as m, n from generate_series(1,2000) a(n))');
184 reset work_mem;