Postgres query left join take too time

Question

i have a problem with this query. it go in loop, I mean query after 15 minutes not finish But if remove one of the left join works where wrong I?

Select distinct a.sito,
Count(distinct a.id_us) as us,
Count (distinct b.id_invmat) as materiali,
Count (distinct c.id_struttura) as Struttura,
Count(distinct d.id_tafonomia) as tafonomia
From us_table as a
Left join invetario_materiali as b on a.sito=b.sito
Left join struttura_table as c on a.sito=c.sito
Left join tafonomia_table as d on a.sito=d.sito
Group by a.sito
Order by us

thanks E

Answer 1

This is a case where correlated subqueries might be the simplest approach:

select s.sito,
      (select count(*) from invetario_materiali m where s.sito = m.sito) as materiali,
      (select count(*) from struttura_tablest where s.sito = st.sito) as Struttura,
      (select count(*) from tafonomia_table t where s.sito = t.sito) as tafonomia
from (select sito, count(*) as us
      from us_table
      group by sito
     ) s
order by us;

This should be much, much faster than your version for two reasons. First, it avoids the outer aggregation. Second, it avoids the Cartesian products among the tables.

You can make this even faster by creating indexes on each of the secondary tables on sito.