Postgres Pro
Downloads
Home   >   mailing lists

Re: left outer join with count - Mailing list pgsql-sql

From Gábor SZŰCS
Subject Re: left outer join with count
Date
Msg-id CAHEufv3tO3f6gyuP_4Gze8QGh3V-O5AVaJa9+RDckB2LK4d76g@mail.gmail.com
Whole thread Raw
In response to left outer join with count  (Gary Stainburn <gary.stainburn@ringways.co.uk>)
Responses Re: left outer join with count
List pgsql-sql
Tree view
Hello Gary,

Depends on way too many things, but assuming
- You are concerned about cardinality of wd Vs SJ
- SJ.sj_id is unique
- there are necessary indexes in place
...
Then I'd recommend subselect in SELECT clause, like:


select sj.*, sr.*, 
    (select count(sj_id) from work_documents wd where wd.sj_id=SJ.sj_id) doc_count from
  service_jobs sj
  left outer join service_receptions sr on sr.sr_id = sj.sj_sr_id;

(Note, haven't verified syntax)

Maybe if you could send explain plans and index info...

Gary Stainburn <gary.stainburn@ringways.co.uk> ezt írta (időpont: 2020. máj. 29., P 18:09):
I have the following select:

select sj.*, sr.*, wd.doc_count from
  service_jobs sj
  left outer join service_receptions sr on sr.sr_id = sj.sj_sr_id
  left outer join (select sj_id, count(sj_id) as doc_count from work_documents wd group by sj_id) wd on wd.wd_sj_id = sj.sj_id;

While the select works, I can't help thinking that the last join is expensive.  Is there a cleaner (quicker) method of doing this?

Gary



pgsql-sql by date:

Previous
From: Gary Stainburn
Date:
Subject: left outer join with count
Next
From: Gary Stainburn
Date:
Subject: Re: left outer join with count