From: Tomasz Myrta <jasiek@klaster.net>
To: Brad Hilton <bhilton@vpop.net>
To: Tomasz Myrta <jasiek@klaster.net>
Cc: jasiek@serwer.skawsoft.com.pl
Cc: pgsql-sql@postgresql.org
Subject: Re: sub-query optimization
Date: Sat, 15 Feb 2003 09:52:13 +0800
Message-ID: <20030215095213.M15935@klaster.net> (raw)
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>
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
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: <20030215095213.M15935@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