pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Haller Christoph <ch@rodos.fzk.de>
To: pgsql-sql@postgresql.org
Subject: Re:
Date: Fri, 07 Sep 2001 15:13:38 METDST
Message-ID: <200109071313.PAA12669@rodos> (raw)

My understanding is you did it the best way. 
Alternatively, you may word your query as 
SELECT i.cname FROM fc_client_info i 
WHERE EXISTS ( SELECT * FROM fc_communication c 
               WHERE c.acode = i.acode 
	       AND c.contactdate = '09/06/2001' )
ORDER BY lower(i.cname);
instead of 
SELECT i.cname FROM fc_client_info i, fc_communication c WHERE i.acode =
c.acode AND c.contactdate = '09/06/2001' ORDER BY lower(cname);

I suppose the correlated subquery using the EXISTS predicate, which 
results in an outer loop join, is much slower than your originally 
used sort-merge join. 
Try, but make sure both tables are well populated, otherwise the 
benchmark result is no good. 
Christoph 



Message-ID: <200109071313.PAA12669@rodos>
Permalink:  ../200109071313.PAA12669@rodos/
Also on:    postgresql.org/message-id/200109071313.PAA12669@rodos

 · 

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: ch@rodos.fzk.de
  Subject: Re:
  In-Reply-To: <200109071313.PAA12669@rodos>

* 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