From: Gary Stainburn <gary.stainburn@ringways.co.uk>
Organization: Ringways Garages Ltd
To: pgsql-sql@postgresql.org
Subject: Re: left outer join with count
Date: Sat, 30 May 2020 20:40:32 +0100
User-Agent: KMail/1.9.10
References: <202005291709.16647.gary.stainburn@ringways.co.uk>
 <CAHEufv3tO3f6gyuP_4Gze8QGh3V-O5AVaJa9+RDckB2LK4d76g@mail.gmail.com>
In-Reply-To: 
 <CAHEufv3tO3f6gyuP_4Gze8QGh3V-O5AVaJa9+RDckB2LK4d76g@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain;
  charset="utf-8"
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline
Message-Id: <202005302040.32647.gary.stainburn@ringways.co.uk>
 
 Content preview:  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... [...]
    
 
 Content analysis details:   (-49.8 points, 15.0 required)
 
  pts rule name              description
 ---- ---------------------- --------------------------------------------------
  -50 ALL_TRUSTED            Passed through trusted hosts only via SMTP
 -1.9 BAYES_00               BODY: Bayes spam probability is 0 to 1%
                             [score: 0.0000]
  0.1 SCORE_RCPTS            Adding score for each recipient
  1.0 MISSING_FROM           Missing From: header
  1.0 RING_SAFE              No description available.
List-Id: <pgsql-sql.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-sql@lists.postgresql.org>
List-Owner: <mailto:pgsql-sql-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-sql>
Precedence: bulk

On Saturday 30 May 2020 05:43:06 G=C3=A1bor SZ=C5=B0CS wrote:
> Hello Gary,
>=20
> 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:
>=20
>=20
> select sj.*, sr.*,
>     (select count(sj_id) from work_documents wd where
> wd.sj_id=3DSJ.sj_id) doc_count
> from
>   service_jobs sj
>   left outer join service_receptions sr on sr.sr_id =3D sj.sj_sr_id;
>=20
> (Note, haven't verified syntax)
>=20
> 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.