pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  

help / color / mirror / Atom feed

From: Dan Langille <dan@langille.org>
To: Gary Stainburn <gary.stainburn@ringways.co.uk>
Cc: pgsql-sql@postgresql.org
Subject: Re: master-detail relationship and count
Date: Fri, 29 Nov 2002 09:24:37 -0500
Message-ID: <3DE73255.29778.E71B8DFA@localhost> (raw)
In-Reply-To: <Pine.LNX.4.44.0211291315420.4251-100000@matrix.gatewaynet.com>
References: <200211291051.07175.gary.stainburn@ringways.co.uk>

On 29 Nov 2002 at 13:16, Achilleus Mantzios wrote:

> On Fri, 29 Nov 2002, Gary Stainburn wrote:
> 
> > As you can see from the extract below, your statement has worked for all
> > landmarks that have links, but ignores any landmarks with out links.  How can
> > I adjust this so that all landmarks are listed, but with a zero count where
> > appropriate?
> 
> Then, use LEFT OUTER JOIN ... USING (),
> in combination with COALESCE().
> 
> (read the docs)

When it comes to outer joins, this page is quite useful:

http://www.postgresql.org/idocs/index.php?explicit-joins.html

I was reading it yesterday when improving the speed of some JOINs.  
The JOIN went from 3440ms to about 18ms when following the advice on 
that page.

I've put the queries and the explain output at 
http://www.freshports.org/tmp/outer-join.txt.  This will be an 
example of an OUTER JOIN but not the master-detail situation which 
occurs elsewhere in the database.

cheers

-- 
Dan Langille : http://www.langille.org/

view thread (8+ messages)  latest in thread

Message-ID: <3DE73255.29778.E71B8DFA@localhost>
Permalink:  ../3DE73255.29778.E71B8DFA@localhost/
Also on:    postgresql.org/message-id/3DE73255.29778.E71B8DFA@localhost

copy link · copy postgr.es

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: dan@langille.org, gary.stainburn@ringways.co.uk
  Subject: Re: master-detail relationship and count
  In-Reply-To: <3DE73255.29778.E71B8DFA@localhost>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox