stats.sql

application/sql

Download

drop table if exists t1;
drop table if exists t2;

create table t1 (a int, b int, c int);
create table t2 (d int, e int, f int);

insert into t1 select i, mod(i,100), mod(i,100) from generate_series(1,1000000) S(i);
insert into t2 select i, mod(i,100), mod(i,100) from generate_series(1,1000000) S(i);

create index on t2 (d);


create or replace 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;
$$;

create or replace function generate_queries() returns table (val int, estimated int, actual int)
language plpgsql as
$$
begin
    for i in 1..10 loop
        select t.estimated, t.actual into estimated, actual from check_estimated_rows(format('select * from t1 join t2 on t1.a = t2.d where t1.b < %s and t2.e < %s', i, i)) t;
        val := i;
        return next;
    end loop;
end;
$$;


set default_statistics_target = 100;

analyze;

select * from generate_queries();

create statistics (mcv) on t1.b, t1.c, t2.e, t2.f from t1 join t2 on (t1.a = t2.d);

analyze;

select * from generate_queries();


drop statistics t1_expr_expr_expr_expr_stat ;


set default_statistics_target = 10000;

analyze;

select * from generate_queries();

create statistics (mcv) on t1.b, t1.c, t2.e, t2.f from t1 join t2 on (t1.a = t2.d);

analyze;

select * from generate_queries();