X-Original-To: pgsql-sql@postgresql.org
Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id 78FC3475AEC
	for <pgsql-sql@postgresql.org>; Fri, 14 Feb 2003 14:33:07 -0500 (EST)
Received: from serwer.skawsoft.com.pl (serwer.skawsoft.com.pl [213.25.37.66])
	by postgresql.org (Postfix) with ESMTP id 37113475A9E
	for <pgsql-sql@postgresql.org>; Fri, 14 Feb 2003 14:33:05 -0500 (EST)
Received: from lamer.pl (localhost [127.0.0.1])
	by serwer.skawsoft.com.pl (Postfix) with ESMTP
	id 453AD2B84B; Fri, 14 Feb 2003 20:24:45 +0100 (CET)
From: "Tomasz Myrta" <jasiek@klaster.net>
To: Brad Hilton <bhilton@vpop.net>, pgsql-sql@postgresql.org
Subject: Re: sub-query optimization
Date: Fri, 14 Feb 2003 20:24:45 +0800
Message-Id: <20030214202445.M57741@klaster.net>
In-Reply-To: <1045246939.29966.22.camel@aragorn.vpop.net>
References: <1045246939.29966.22.camel@aragorn.vpop.net>
X-Mailer: Open WebMail 1.62 20020221
X-OriginatingIP: 217.96.97.171 (jasiek)
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-2
X-Virus-Scanned: by AMaViS new-20020517
X-Archive-Number: 200302/344
X-Sequence-Number: 11742

> Hello,
> 
> I am hoping someone can help explain why modifying the following 
> query can effect such a huge change in speed.  The query is:
> 
>       select * from articles
>       where exists
>        ( select 1 from article_categories
>          where
>          article_categories.article_id = articles.id and
>          article_categories.category_id is null
>        )
> 

Can you test these two queries?
 select * from
  (select article_id from article_categories where category_id is null
   group by article_id) X
    join articles using (article_id);

  select <fields> from
   article_categories
   join articles using (article_id)
  where category_id is null
   group by <fields>

 Above queries will need index on article_id
 I'm not sure if it helps, but they are the only solutions in my
 mind ;-)

 Can you say anything about data statistics in your tables? How
 many rows are with category_id=null?

 I looked into query definition once again. Your query doesn't make
 sense - article_categories have not null category_id... What do you really
 want to do?

Regards,
Tomasz Myrta