X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.130]) by svr1.postgresql.org (Postfix) with ESMTP id E9DD1D1B518 for ; Tue, 9 Sep 2003 03:14:44 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new, port 10024) with ESMTP id 85321-08 for ; Tue, 9 Sep 2003 00:14:34 -0300 (ADT) Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by svr1.postgresql.org (Postfix) with ESMTP id 071C7D1B503 for ; Tue, 9 Sep 2003 00:14:34 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.9/8.12.9) with ESMTP id h893ETAn019154; Mon, 8 Sep 2003 23:14:29 -0400 (EDT) To: Josh Berkus Cc: pgsql-performance@postgresql.org Subject: Re: Quick question In-reply-to: <200309082002.18830.josh@agliodbs.com> References: <200309082002.18830.josh@agliodbs.com> Comments: In-reply-to Josh Berkus message dated "Mon, 08 Sep 2003 20:02:18 -0700" Date: Mon, 08 Sep 2003 23:14:29 -0400 Message-ID: <19153.1063077269@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200309/141 X-Sequence-Number: 3507 Josh Berkus writes: > Back in the 7.0 days, > WHERE EXISTS (SELECT * FROM a WHERE condition) > was significantly slower on broad tables than > WHERE EXISTS (SELECT small_col FROM a WHERE condition) > Is this still true, or something that's been fixed in the last 3 versions? It's still true that all the sub-select's output columns will be evaluated. Given that this happens for at most one row, I'm not sure how significant the hit really is. But it's annoying, seeing that the outer EXISTS doesn't care what the column values are. > Joe Celko is making fun of me because Oracle doesn't have this performance > issue. Perhaps Joe can tell us exactly which part of SQL92 says it's okay not to evaluate side-effect-producing functions in the targetlist of an EXISTS subselect. I would like to make the system change the targetlist to just "SELECT 1" in an EXISTS subquery. But I'm slightly concerned about changing the semantics of existing queries. If someone can produce proof that this is allowed (or even better, required) by the SQL spec, it'd be easier... regards, tom lane