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.
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
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:');
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
46 -- Replace any numeric word with just '0'
47 ln := regexp_replace(ln, '\m\d+\M', '0', 'g');
54 -- Disable JIT, or we'll get different output on machines where that's been
58 -- Similarly, disable track_io_timing, to avoid output differences when
60 set track_io_timing = off;
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;
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.
84 set local plan_cache_mode = force_generic_plan;
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}';
91 -- GENERIC_PLAN option
93 select explain_filter('explain (generic_plan) select unique1 from tenk1 where thousand = $1');
95 select explain_filter('explain (analyze, generic_plan) select unique1 from tenk1 where thousand = $1');
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');
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.
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;
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}'
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)
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))');