test.sql

application/octet-stream

Download

-- This function checks which clauses have extended statistics applied
create or replace function check_clause(text, text) returns table (clause text, extstats text)
language plpgsql as
$$
declare
	sql text;
    ln text;
    tmp text[];
    c text;
    s text;
begin
	if ($1 is not null) then
		sql := format('explain (stats, costs off, %s) %s', $1, $2);
	else
		sql := format('explain (stats, costs off) %s', $2);
	end if;

    for ln in
        execute sql
    loop
        tmp := regexp_match(ln, 'Filter: .*|Group Key: .*');
        if tmp is not null then
            c = tmp[1]::text;
        end if;

        tmp := regexp_match(ln, 'Ext Stats: (.*)');
        if tmp is not null then
            s = tmp[1]::text;
        end if;

        if (c is not null) and (s is not null) then
            return query select c, s;
            c := null;
            s := null;
        end if;
    end loop;
end;
$$;


-- prepare
create table t (a int, b int);
insert into t select mod(i, 10), mod(i, 10) from generate_series(1, 100000) s(i);

-- without extended stats
-- where
EXPLAIN (stats, costs off) select * from t where a = 1 and b = 1;
SELECT * FROM check_clause(null, 'select * from t where a = 1 and b = 1');
-- group by
EXPLAIN (stats, costs off) select 1 from t group by a, b;
SELECT * FROM check_clause(null, 'select * from t group by a, b');

-- create extended stats
create statistics s on a, b from t;
analyze t;
\dX

-- show extended stats without verbose option
-- where
-- EXPLAIN (stats, costs off) select * from t where a = 1 and b = 1;
SELECT * FROM check_clause(null, 'select * from t where a = 1 and b = 1');
-- group by
-- EXPLAIN (stats, costs off) select 1 from t group by a, b;
SELECT * FROM check_clause(null, 'select * from t group by a, b');

-- use verbose option
-- where
SELECT * FROM check_clause('verbose', 'select * from t where a = 1 and b = 1');
-- group by
SELECT * FROM check_clause('verbose', 'select * from t group by a, b');

-- display Ext Stats: twice?!
-- HashAgg
EXPLAIN (stats, costs off) select 1 from t group by a, b;
SELECT * FROM check_clause(null, 'select * from t group by a, b');
-- GroupAgg
set enable_hashagg to off;
EXPLAIN (stats, costs off) select 1 from t group by a, b;
SELECT * FROM check_clause(null, 'select * from t group by a, b');
set enable_hashagg to on;
-- Parallel
-- shows twice
set min_parallel_table_scan_size to '0';
EXPLAIN (stats, costs off) select 1 from t group by a, b;
SELECT * FROM check_clause(null, 'select * from t group by a, b');
set min_parallel_table_scan_size to default;
-- Parallel Hash
-- This is the reason to show ExtStats twice
set enable_parallel_hash to off;
EXPLAIN (stats, costs off) select 1 from t group by a, b;
SELECT * FROM check_clause(null, 'select * from t group by a, b');
set enable_parallel_hash to default;

-- prepare statement: not display Ext Stats because Topmost command is EXECUTE,
-- so isExplain_stats flag seems false
PREPARE ps (INT, INT) AS SELECT * FROM t WHERE a = $1 AND b = $2;
EXPLAIN(stats, costs on, analyze) EXECUTE ps(5, 5);
SELECT * FROM check_clause(null, 'EXECUTE ps(5, 5)');

-- crean up
DROP TABLE t;
DROP FUNCTION check_clause(text, text);
\dX