agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedFrom: Gary Stainburn <gary.stainburn@ringways.co.uk>
To: pgsql-sql@postgresql.org
Subject: Re: left outer join with count
Date: Sat, 30 May 2020 20:40:32 +0100
Message-ID: <202005302040.32647.gary.stainburn@ringways.co.uk> (raw)
In-Reply-To: <CAHEufv3tO3f6gyuP_4Gze8QGh3V-O5AVaJa9+RDckB2LK4d76g@mail.gmail.com>
References: <202005291709.16647.gary.stainburn@ringways.co.uk>
<CAHEufv3tO3f6gyuP_4Gze8QGh3V-O5AVaJa9+RDckB2LK4d76g@mail.gmail.com>
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.
view thread (3+ messages)
Message-ID: <202005302040.32647.gary.stainburn@ringways.co.uk>
Permalink: ../202005302040.32647.gary.stainburn@ringways.co.uk/
Also on: postgresql.org/message-id/202005302040.32647.gary.stainburn@ringways.co.uk
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: gary.stainburn@ringways.co.uk
Subject: Re: left outer join with count
In-Reply-To: <202005302040.32647.gary.stainburn@ringways.co.uk>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox