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)? ---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
