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

help / color / mirror / Atom feed
From: Stuart Rison <rison@biochemistry.ucl.ac.uk>
To: Albert REINER <areiner@tph.tuwien.ac.at>
Cc: 'pgsql-sql@hub.org' <pgsql-sql@hub.org>
Subject: Re: [SQL] comparing 2 tables. . .
Date: Mon, 27 Sep 1999 11:59:30 +0100 (BST)
Message-ID: <Pine.LNX.4.10.9909271153130.28372-100000@bsmlx17> (raw)
In-Reply-To: <19990924224748.A182@frithjof>

Well, I guess officially you're supposed to be able to decode an EXPLAIN
of each statement but I'm not quite that savvy (yet).

With regards to this specific examples, I seem to remember a number of
previous postings that say that (NOT) EXISTS type statements are much
faster that (NOT) IN (SELECT...) type statements (because that kind of
sub-select is currently sub-optimally implemented in PG).

regards,

Stuart.

On Fri, 24 Sep 1999, Albert REINER wrote:

> On Fri, Sep 24, 1999 at 03:32:51PM +0100, Stuart Rison wrote:
> ...
> > Try:
> > 
> > SELECT tab1.pkey FROM <your_table> tab1 WHERE tab1.pkey NOT IN (SELECT
> > tab2.pkey FROM <your_other_table> tab2.pkey);
> > 
> > or else (faster):
> > 
> > SELECT tab1.pkey FROM <your_table> tab1 WHERE NOT EXISTS (SELECT 1 FROM
> > <your_other_table> tab2 WHERE tab1.pkey=tab2.pkey);
> 
> Maybe this is trivial, but...
> 
> Is there a simple way to see that/why the last query is faster?
> 
> Albert.
> 
> -- 
> 
> ---------------------------------------------------------------------------
>   Post an / Mail to / Skribu al: Albert Reiner <areiner@tph.tuwien.ac.at>
> ---------------------------------------------------------------------------
> 
> ************
> 

Stuart C. G. Rison
Department of Biochemistry and Molecular Biology
6th floor, Darwin Building, University College London (UCL)
Gower Street, London, WC1E 6BT, United Kingdom
Tel. 0207 504 2303, Fax. 0207 380 7033
e-mail: rison@biochem.ucl.ac.uk




view thread (14+ messages)  latest in thread

Message-ID: <Pine.LNX.4.10.9909271153130.28372-100000@bsmlx17>
Permalink:  ../Pine.LNX.4.10.9909271153130.28372-100000@bsmlx17/
Also on:    postgresql.org/message-id/Pine.LNX.4.10.9909271153130.28372-100000@bsmlx17

 · 

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: rison@biochemistry.ucl.ac.uk, areiner@tph.tuwien.ac.at, pgsql-sql@hub.org
  Subject: Re: [SQL] comparing 2 tables. . .
  In-Reply-To: <Pine.LNX.4.10.9909271153130.28372-100000@bsmlx17>

* 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