From: jasiek@serwer.skawsoft.com.pl
To: Brad Hilton <bhilton@vpop.net>
Cc: 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:45:00 +0100
Message-ID: <20030215084500.GA30253@serwer> (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.
> 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.
What about adding "group by a.field1,a.field2..."? It will eliminate
duplicates.
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@serwer.skawsoft.com.pl, bhilton@vpop.net, jasiek@klaster.net
Subject: Re: sub-query optimization
In-Reply-To: <20030215084500.GA30253@serwer>
* 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