X-Original-To: pgsql-sql@postgresql.org Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id A0338475D17 for ; Fri, 14 Feb 2003 17:08:26 -0500 (EST) Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by postgresql.org (Postfix) with ESMTP id 0B203475CB4 for ; Fri, 14 Feb 2003 17:08:26 -0500 (EST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.6/8.12.6) with ESMTP id h1EM8R5u011482; Fri, 14 Feb 2003 17:08:27 -0500 (EST) To: Brad Hilton Cc: pgsql-sql@postgresql.org Subject: Re: sub-query optimization In-reply-to: <1045246939.29966.22.camel@aragorn.vpop.net> References: <1045246939.29966.22.camel@aragorn.vpop.net> Comments: In-reply-to Brad Hilton message dated "14 Feb 2003 10:22:19 -0800" Date: Fri, 14 Feb 2003 17:08:27 -0500 Message-ID: <11481.1045260507@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200302/351 X-Sequence-Number: 11749 Brad Hilton writes: > 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 > ) > ... If I modify the query slightly: > -------- > select 1 from article_categories > --> > select 1 from articles, article_categories > --------- > the query takes 98 msec. Yeah, because then the sub-query is a constant (it doesn't depend on the current outer row at all) and so it is only evaluated once, not once per outer row. Unfortunately, that approach probably gives the wrong answers... regards, tom lane