X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 9F15ED1E2A7 for ; Fri, 16 Apr 2004 20:45:00 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 28618-06 for ; Fri, 16 Apr 2004 20:44:57 -0300 (ADT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id 9E292D1D085 for ; Fri, 16 Apr 2004 20:44:58 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.11/8.12.11) with ESMTP id i3GNj1lS003803; Fri, 16 Apr 2004 19:45:02 -0400 (EDT) To: Litao Wu Cc: pgsql-performance@postgresql.org Subject: Re: sunquery and estimated rows In-reply-to: <20040416214529.10468.qmail@web13125.mail.yahoo.com> References: <20040416214529.10468.qmail@web13125.mail.yahoo.com> Comments: In-reply-to Litao Wu message dated "Fri, 16 Apr 2004 14:45:29 -0700" Date: Fri, 16 Apr 2004 19:45:01 -0400 Message-ID: <3802.1082159101@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200404/233 X-Sequence-Number: 6533 Litao Wu writes: > When I included a subquery, the estimated rows (1240) > is way too high as shown in the following example. > select * from test > where scope=(select 10); The planner sees that as "where scope = " and falls back to a default estimate. It won't simplify a sub-select to a constant. (Some people consider that a feature ;-).) The estimate should still be derived from the statistics for the scope column, but it will just depend on the number of distinct values for the column and not on the specific comparison constant. regards, tom lane