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