Re: sub-query optimization - Mailing list pgsql-sql

On Fri, 2003-02-14 at 04:59, Tomasz Myrta wrote:
> 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
> > 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),

I don't think the article_categories primary key can be used in my query
since I'm also joining against category_map.  Articles can live in
multiple categories.  What my query is attempting is (in english terms):

select all articles that live in non-restricted categories at or below a
top-level category (id=1 in this case).

If I just utilize article_categories primary key, I could end up with
duplicate articles since articles can live in multiple categories.


>  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'
>   

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?

select a.*
fromcategories c cross join category_map mjoin 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)
wherem.parent_id=1 andnot c.restrict_views andm.child_id = c.id anda.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.

-Brad