Date: Fri, 14 Feb 2003 12:04:45 -0800 (PST)
From: Stephan Szabo <sszabo@megazone23.bigpanda.com>
To: Brad Hilton <bhilton@vpop.net>
Cc: <pgsql-sql@postgresql.org>
Subject: Re: sub-query optimization
In-Reply-To: <1045246939.29966.22.camel@aragorn.vpop.net>
Message-ID: <20030214115945.V64558-100000@megazone23.bigpanda.com>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII


On 14 Feb 2003, Brad Hilton wrote:

> I am hoping someone can help explain why modifying the following query
> can effect such a huge change in speed.  The query is:
>
>       select * from articles
>       where exists
>        ( select 1 from article_categories
>          where
>          article_categories.article_id = articles.id and
>          article_categories.category_id is null
>        )
>
> The original query was much more complex, but I have trimmed it down to
> highlight the problem.  The query above also manifests the problem.  OK,
> the above query (with 100,000 records in the articles table) takes 1292
> msec (see output below).  If I modify the query slightly:
>
> --------
> select 1 from article_categories
>   -->
> select 1 from articles, article_categories
> ---------

After putting the latter in the subselect do you actually have the same
query?  In one case articles is an outer reference for the particular
row.  In the other it's a reference to the copy of articles in the
subselect.  Wouldn't that give the wrong results when you have any matches
(since there'd exist a row from the subselect even if it wasn't the one
matching the outer query)?