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 A97D5D1D803 for ; Fri, 16 Apr 2004 18:45:31 -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 92168-09 for ; Fri, 16 Apr 2004 18:45:27 -0300 (ADT) Received: from web13125.mail.yahoo.com (web13125.mail.yahoo.com [216.136.174.143]) by svr1.postgresql.org (Postfix) with SMTP id 8289CD1B515 for ; Fri, 16 Apr 2004 18:45:28 -0300 (ADT) Message-ID: <20040416214529.10468.qmail@web13125.mail.yahoo.com> Received: from [63.78.248.48] by web13125.mail.yahoo.com via HTTP; Fri, 16 Apr 2004 14:45:29 PDT Date: Fri, 16 Apr 2004 14:45:29 -0700 (PDT) From: Litao Wu Subject: sunquery and estimated rows To: pgsql-performance@postgresql.org MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200404/229 X-Sequence-Number: 6529 Hi, When I included a subquery, the estimated rows (1240) is way too high as shown in the following example. Can someone explain why? Because of this behavior, some of our queries use hash join instead of nested loop. Thanks, select version(); version ------------------------------------------------------------- PostgreSQL 7.3.4 on i686-pc-linux-gnu, compiled by GCC 2.96 (1 row) \d test Table "public.test" Column | Type | Modifiers ---------+--------------------------+----------- id | integer | name | character varying(255) | d_id | integer | c_id | integer | r_id | integer | u_id | integer | scope | integer | active | integer | created | timestamp with time zone | typ | integer | Indexes: test_scope_idx btree (scope) reindex table test; vacuum full analyze test; select count(*) from test; count ------- 4959 (1 row) select count(*) from test where scope=10; count ------- 10 (1 row) explain analyze select * from test where scope=10; -- so far so good, estimate 12 rows, actual 10 rows QUERY PLAN ---------------------------------------------------------------------------------------------------------------------- Index Scan using test_scope_idx on test (cost=0.00..4.35 rows=12 width=59) (actual time=0.04..0.11 rows=10 loops=1) Index Cond: (scope = 10) Total runtime: 0.23 msec (3 rows) explain analyze select * from test where scope=(select 10); -- estimate rows is way too high, do not why???? QUERY PLAN ------------------------------------------------------------------------------------------------------------------------- Index Scan using test_scope_idx on test (cost=0.00..40.74 rows=1240 width=59) (actual time=0.06..0.13 rows=10 loops=1) Index Cond: (scope = $0) InitPlan -> Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.01..0.01 rows=1 loops=1) Total runtime: 0.22 msec (5 rows) __________________________________ Do you Yahoo!? Yahoo! Tax Center - File online by April 15th http://taxes.yahoo.com/filing.html