| -- |
| -- EXPLAIN |
| -- |
| -- There are many test cases elsewhere that use EXPLAIN as a vehicle for |
| -- checking something else (usually planner behavior). This file is |
| -- concerned with testing EXPLAIN in its own right. |
| -- |
| |
| -- To produce stable regression test output, it's usually necessary to |
| -- ignore details such as exact costs or row counts. These filter |
| -- functions replace changeable output details with fixed strings. |
| |
| create function explain_filter(text) returns setof text |
| language plpgsql as |
| $$ |
| declare |
| ln text; |
| begin |
| for ln in execute $1 |
| loop |
| -- Replace any numeric word with just 'N' |
| ln := regexp_replace(ln, '\m\d+\M', 'N', 'g'); |
| -- In sort output, the above won't match units-suffixed numbers |
| ln := regexp_replace(ln, '\m\d+kB', 'NkB', 'g'); |
| -- Ignore text-mode buffers output because it varies depending |
| -- on the system state |
| CONTINUE WHEN (ln ~ ' +Buffers: .*'); |
| -- Ignore text-mode "Planning:" line because whether it's output |
| -- varies depending on the system state |
| CONTINUE WHEN (ln = 'Planning:'); |
| return next ln; |
| end loop; |
| end; |
| $$; |
| |
| -- To produce valid JSON output, replace numbers with "0" or "0.0" not "N" |
| create function explain_filter_to_json(text) returns jsonb |
| language plpgsql as |
| $$ |
| declare |
| data text := ''; |
| ln text; |
| begin |
| for ln in execute $1 |
| loop |
| -- Replace any numeric word with just '0' |
| ln := regexp_replace(ln, '\m\d+\M', '0', 'g'); |
| data := data || ln; |
| end loop; |
| return data::jsonb; |
| end; |
| $$; |
| |
| -- Simple cases |
| |
| select explain_filter('explain select * from int8_tbl i8'); |
| select explain_filter('explain (analyze) select * from int8_tbl i8'); |
| select explain_filter('explain (analyze, verbose) select * from int8_tbl i8'); |
| select explain_filter('explain (analyze, buffers, format text) select * from int8_tbl i8'); |
| select explain_filter('explain (analyze, buffers, format json) select * from int8_tbl i8'); |
| select explain_filter('explain (analyze, buffers, format xml) select * from int8_tbl i8'); |
| select explain_filter('explain (analyze, buffers, format yaml) select * from int8_tbl i8'); |
| select explain_filter('explain (buffers, format text) select * from int8_tbl i8'); |
| select explain_filter('explain (buffers, format json) select * from int8_tbl i8'); |
| |
| -- SETTINGS option |
| -- We have to ignore other settings that might be imposed by the environment, |
| -- so printing the whole Settings field unfortunately won't do. |
| |
| begin; |
| set local plan_cache_mode = force_generic_plan; |
| select true as "OK" |
| from explain_filter('explain (settings) select * from int8_tbl i8') ln |
| where ln ~ '^ *Settings: .*plan_cache_mode = ''force_generic_plan'''; |
| select explain_filter_to_json('explain (settings, format json) select * from int8_tbl i8') #> '{0,Settings,plan_cache_mode}'; |
| rollback; |
| |
| -- |
| -- Test production of per-worker data |
| -- |
| -- Unfortunately, because we don't know how many worker processes we'll |
| -- actually get (maybe none at all), we can't examine the "Workers" output |
| -- in any detail. We can check that it parses correctly as JSON, and then |
| -- remove it from the displayed results. |
| |
| -- Serializable isolation would disable parallel query, so explicitly use an |
| -- arbitrary other level. |
| begin isolation level repeatable read; |
| -- encourage use of parallel plans |
| set parallel_setup_cost=0; |
| set parallel_tuple_cost=0; |
| set min_parallel_table_scan_size=0; |
| set max_parallel_workers_per_gather=4; |
| |
| select jsonb_pretty( |
| explain_filter_to_json('explain (analyze, verbose, buffers, format json) |
| select * from tenk1 order by tenthous') |
| -- remove "Workers" node of the Seq Scan plan node |
| #- '{0,Plan,Plans,0,Plans,0,Workers}' |
| -- remove "Workers" node of the Sort plan node |
| #- '{0,Plan,Plans,0,Workers}' |
| -- Also remove its sort-type fields, as those aren't 100% stable |
| #- '{0,Plan,Plans,0,Sort Method}' |
| #- '{0,Plan,Plans,0,Sort Space Type}' |
| ); |
| |
| rollback; |