tst.sql

application/sql

Download

drop table if exists btg;

set jit=off;
set enable_hashagg=off;
set max_parallel_workers= 0;
set max_parallel_workers_per_gather = 0;
--set work_mem='64MB';

select
		1 as n,
        format('%60s', (i%2)::int) as v,
		(random() * 1000.0) as r
        into btg
from
        generate_series(1, 1000000) i;

create statistics x on n, v, r from btg;

vacuum btg;
analyze btg;

-- 1opt
explain analyze
select
        count(*)
from
        btg
group by
        v, r;

-- 2opt
explain analyze
select
        count(*)
from
        btg
group by
        n, v, r
order by n;

set debug_enable_cheapest_group_by=off;

-- 1noopt
explain analyze
select
        count(*)
from
        btg
group by
        v, r;

-- 2nopt
explain analyze
select
        count(*)
from
        btg
group by
        n, v, r
order by n;