From: "Tomasz Myrta" <jasiek@klaster.net>
To: Brad Hilton <bhilton@vpop.net>, Tomasz Myrta <jasiek@klaster.net>
Cc: jasiek@serwer.skawsoft.com.pl, pgsql-sql@postgresql.org
Subject: Re: sub-query optimization
Date: Sat, 15 Feb 2003 09:52:13 +0800
Message-Id: <20030215095213.M15935@klaster.net>
In-Reply-To: <1045262371.29955.124.camel@aragorn.vpop.net>
References: <1045246939.29966.22.camel@aragorn.vpop.net>
	<20030214192113.GA26987@serwer>
	<1045251747.29974.45.camel@aragorn.vpop.net>
	<20030214205922.M13630@klaster.net>
	<1045262371.29955.124.camel@aragorn.vpop.net>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-2

On Fri, Feb 14, 2003 at 02:39:31PM -0800, Brad Hilton wrote:
> If I just utilize article_categories primary key, I could end up with
> duplicate articles since articles can live in multiple categories.
You can use group by to eliminate duplicates.

> In case I'm not understanding your suggestiong perfectly, I tried to
> flesh it out a bit more.  Does the following query match your
> suggestion?
It looks ok now. Probably it needs some cosmetics changes.
>
> select a.*
> from
>  categories c cross join category_map m
>  join article_categories ac on (c.id = ac.category_id and m.child_id =
> ac.category_id)
>  join articles a on (a.id = ac.article_id)
> where
>  m.parent_id=1 and
>  not c.restrict_views and
>  m.child_id = c.id and
>  a.post_status='publish'
>
> Unfortunately, this query returns duplicate articles (see explanation
> above), and is fairly slow.  Maybe I didn't follow your initial query
> properly.
Can you send explain analyze this query? Maybe table
joins should be reordered or they need other indexes they have?

Tomasz