From: Tomasz Myrta <jasiek@klaster.net>
To: Brad Hilton <bhilton@vpop.net>
To: 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> (raw)
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>
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
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: jasiek@klaster.net, bhilton@vpop.net, jasiek@serwer.skawsoft.com.pl
Subject: Re: sub-query optimization
In-Reply-To: <20030214205922.M13630@klaster.net>
* 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