X-Original-To: pgsql-sql@postgresql.org
Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id BCC6C475A80
	for <pgsql-sql@postgresql.org>; Fri, 14 Feb 2003 15:42:22 -0500 (EST)
Received: from serwer.skawsoft.com.pl (serwer.skawsoft.com.pl [213.25.37.66])
	by postgresql.org (Postfix) with ESMTP id 202AA475A6D
	for <pgsql-sql@postgresql.org>; Fri, 14 Feb 2003 15:42:22 -0500 (EST)
Received: from lamer.pl (localhost [127.0.0.1])
	by serwer.skawsoft.com.pl (Postfix) with ESMTP
	id 2FFE32B84B; Fri, 14 Feb 2003 20:59:22 +0100 (CET)
From: "Tomasz Myrta" <jasiek@klaster.net>
To: Brad Hilton <bhilton@vpop.net>, jasiek@serwer.skawsoft.com.pl
Cc: pgsql-sql@postgresql.org
Subject: Re: sub-query optimization
Date: Fri, 14 Feb 2003 20:59:22 +0800
Message-Id: <20030214205922.M13630@klaster.net>
In-Reply-To: <1045251747.29974.45.camel@aragorn.vpop.net>
References: <1045246939.29966.22.camel@aragorn.vpop.net>
	<20030214192113.GA26987@serwer>
	<1045251747.29974.45.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/348
X-Sequence-Number: 11746

Brad Hilton wrote:
<cut>
> select * from articles where exists
>   (select 1 from article_categories, categories, category_map
>    where
>    article_categories.article_id = articles.id and
>    categories.restrict_views = FALSE and
>    article_categories.category_id = categories.id and
>    category_map.parent_id = 1 and
>    category_map.child_id = categories.id and
>    category_map.child_id = article_categories.category_id and
>    articles.post_status = 'publish'
>   )
>  and
> post_status = 'publish'

According to your table definition I can say, that you don't need subselect 
and exists, because 1 row from article and 1 row from categories have only 1 
hit row in articles_categories (primary key), so you can rewrite your query 
as simple joins:
(Query is only a hint, it probably won't work)

select a.*
from
  categories_c cross join category_map m  
  join articles a on (child_id=category_id)
  join articles_categories ac using (article_id,category_id)
where
  m.parent_id=1 and not c.restrict_views;
  and a.post_status='publish'
  

You can change join order depending on your table stats.

Regards,
Tomasz Myrta