LINUX.ORG.RU

История изменений

Исправление 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
) _