История изменений
Исправление drsm, (текущая версия) :
with
a as (select distinct (generate_series(1,1000) * random())::int as id),
b as (select (generate_series(1,10000) * random())::int as id, 'b' as str),
c as (select (generate_series(1,10000) * random())::int as id, 'c' as str),
d as (select (generate_series(1,10000) * random())::int as id, 'd' as str),
e as (select (generate_series(1,10000) * random())::int as id, 'e' as str),
f as (select (generate_series(1,10000) * random())::int as id, 'f' as str)
select count(*) from (
select sum(x), id from (
select count(b.id) as x, a.id from a inner join b on a.id = b.id group by 2
union all
select count(c.id) as x, a.id from a inner join c on a.id = c.id group by 2
union all
select count(d.id) as x, a.id from a inner join d on a.id = d.id group by 2
union all
select count(e.id) as x, a.id from a inner join e on a.id = e.id group by 2
union all
select count(f.id) as x, a.id from a inner join f on a.id = f.id group by 2
) _ group by 2
having sum(x) > 40
) _
Исходная версия drsm, :
explain analyze
with
a as (select distinct (generate_series(1,1000) * random())::int as id),
b as (select (generate_series(1,10000) * random())::int as id, 'b' as str),
c as (select (generate_series(1,10000) * random())::int as id, 'c' as str),
d as (select (generate_series(1,10000) * random())::int as id, 'd' as str),
e as (select (generate_series(1,10000) * random())::int as id, 'e' as str),
f as (select (generate_series(1,10000) * random())::int as id, 'f' as str)
select count(*) from (
select sum(x), id from (
select count(b.id) as x, a.id from a inner join b on a.id = b.id group by 2
union all
select count(c.id) as x, a.id from a inner join c on a.id = c.id group by 2
union all
select count(d.id) as x, a.id from a inner join d on a.id = d.id group by 2
union all
select count(e.id) as x, a.id from a inner join e on a.id = e.id group by 2
union all
select count(f.id) as x, a.id from a inner join f on a.id = f.id group by 2
) _ group by 2
having sum(x) > 40
) _