test.sql

application/octet-stream

Download

-- check the number of estimated/actual rows in the top node
create function check_estimated_rows(text) returns table (estimated int, actual int)
language plpgsql as
$$
declare
    ln text;
    tmp text[];
    first_row bool := true;
begin
    for ln in
        execute format('explain analyze %s', $1)
    loop
        if first_row then
            first_row := false;
            tmp := regexp_match(ln, 'rows=(\d*) .* rows=(\d*)');
            return query select tmp[1]::int, tmp[2]::int;
        end if;
    end loop;
end;
$$;

-- This function checks which clauses have extended statistics applied
create function check_clause(text) returns table (clause text, extstats text)
language plpgsql as
$$
declare
    ln text;
    tmp text[];
    c text;
    s text;
begin
    for ln in
        execute format('explain (stats, costs off) %s', $1)
    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, analyze) select * from t where a = 1 and b = 1;
SELECT * FROM check_clause('select * from t where a = 1 and b = 1');
-- group by
EXPLAIN (stats) select 1 from t group by a, b;
SELECT * FROM check_clause('select * from t group by a, b');

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

-- with extended stats
-- where
EXPLAIN (stats, analyze) select * from t where a = 1 and b = 1;
SELECT * FROM check_clause('select * from t where a = 1 and b = 1');
-- group by
EXPLAIN (stats, analyze) select 1 from t group by a, b;
SELECT * FROM check_clause('select * from t group by a, b');
-- display Ext Stats: twice?!
set min_parallel_table_scan_size to '0.01MB';
EXPLAIN (stats, analyze) select 1 from t group by a, b;
SELECT * FROM check_clause('select * from t group by a, b');
-- 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, analyze) EXECUTE ps(5,5);

-- crean up
DROP TABLE t;
DROP FUNCTION check_clause, check_estimated_rows;
\dX