agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedleft outer join with count
3+ messages / 2 participants
[nested] [flat]
* left outer join with count
@ 2020-05-29 16:09 Gary Stainburn <gary.stainburn@ringways.co.uk>
2020-05-30 04:43 ` Re: left outer join with count Gábor SZŰCS <surrano@gmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: Gary Stainburn @ 2020-05-29 16:09 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: left outer join with count
2020-05-29 16:09 left outer join with count Gary Stainburn <gary.stainburn@ringways.co.uk>
@ 2020-05-30 04:43 ` Gábor SZŰCS <surrano@gmail.com>
2020-05-30 19:40 ` Re: left outer join with count Gary Stainburn <gary.stainburn@ringways.co.uk>
0 siblings, 1 reply; 3+ messages in thread
From: Gábor SZŰCS @ 2020-05-30 04:43 UTC (permalink / raw)
To: Gary Stainburn <gary.stainburn@ringways.co.uk>; +Cc: pgsql-sql
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
>
>
>
>
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: left outer join with count
2020-05-29 16:09 left outer join with count Gary Stainburn <gary.stainburn@ringways.co.uk>
2020-05-30 04:43 ` Re: left outer join with count Gábor SZŰCS <surrano@gmail.com>
@ 2020-05-30 19:40 ` Gary Stainburn <gary.stainburn@ringways.co.uk>
0 siblings, 0 replies; 3+ messages in thread
From: Gary Stainburn @ 2020-05-30 19:40 UTC (permalink / raw)
To: pgsql-sql
On Saturday 30 May 2020 05:43:06 Gábor SZŰCS wrote:
> 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...
Thank you for this. I ended up doing it as two separate queries. Seems to be quicker.
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2020-05-30 19:40 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-05-29 16:09 left outer join with count Gary Stainburn <gary.stainburn@ringways.co.uk>
2020-05-30 04:43 ` Gábor SZŰCS <surrano@gmail.com>
2020-05-30 19:40 ` Gary Stainburn <gary.stainburn@ringways.co.uk>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox