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--
8
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.
12
13create function explain_filter(text) returns setof text
14language plpgsql as
15$$
16declare
17    ln text;
18begin
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;
33end;
34$$;
35
36-- To produce valid JSON output, replace numbers with "0" or "0.0" not "N"
37create function explain_filter_to_json(text) returns jsonb
38language plpgsql as
39$$
40declare
41    data text := '';
42    ln text;
43begin
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;
51end;
52$$;
53
54-- Simple cases
55
56select explain_filter('explain select * from int8_tbl i8');
57select explain_filter('explain (analyze) select * from int8_tbl i8');
58select explain_filter('explain (analyze, verbose) select * from int8_tbl i8');
59select explain_filter('explain (analyze, buffers, format text) select * from int8_tbl i8');
60select explain_filter('explain (analyze, buffers, format json) select * from int8_tbl i8');
61select explain_filter('explain (analyze, buffers, format xml) select * from int8_tbl i8');
62select explain_filter('explain (analyze, buffers, format yaml) select * from int8_tbl i8');
63select explain_filter('explain (buffers, format text) select * from int8_tbl i8');
64select explain_filter('explain (buffers, format json) select * from int8_tbl i8');
65
66-- SETTINGS option
67-- We have to ignore other settings that might be imposed by the environment,
68-- so printing the whole Settings field unfortunately won't do.
69
70begin;
71set local plan_cache_mode = force_generic_plan;
72select true as "OK"
73  from explain_filter('explain (settings) select * from int8_tbl i8') ln
74  where ln ~ '^ *Settings: .*plan_cache_mode = ''force_generic_plan''';
75select explain_filter_to_json('explain (settings, format json) select * from int8_tbl i8') #> '{0,Settings,plan_cache_mode}';
76rollback;
77
78--
79-- Test production of per-worker data
80--
81-- Unfortunately, because we don't know how many worker processes we'll
82-- actually get (maybe none at all), we can't examine the "Workers" output
83-- in any detail.  We can check that it parses correctly as JSON, and then
84-- remove it from the displayed results.
85
86begin;
87-- encourage use of parallel plans
88set parallel_setup_cost=0;
89set parallel_tuple_cost=0;
90set min_parallel_table_scan_size=0;
91set max_parallel_workers_per_gather=4;
92
93select jsonb_pretty(
94  explain_filter_to_json('explain (analyze, verbose, buffers, format json)
95                         select * from tenk1 order by tenthous')
96  -- remove "Workers" node of the Seq Scan plan node
97  #- '{0,Plan,Plans,0,Plans,0,Workers}'
98  -- remove "Workers" node of the Sort plan node
99  #- '{0,Plan,Plans,0,Workers}'
100  -- Also remove its sort-type fields, as those aren't 100% stable
101  #- '{0,Plan,Plans,0,Sort Method}'
102  #- '{0,Plan,Plans,0,Sort Space Type}'
103);
104
105rollback;
106
107set compute_query_id = on;
108select explain_filter('explain (verbose) select * from int8_tbl i8');
109