sunquery and estimated rows

From: Litao Wu <litaowu(at)yahoo(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: sunquery and estimated rows
Date: 2004-04-16 21:45:29
Message-ID: 20040416214529.10468.qmail@web13125.mail.yahoo.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

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

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Ron St-Pierre 2004-04-16 21:54:55 Re: Index Problem?
Previous Message Chris Kratz 2004-04-16 21:26:32 Re: Long running queries degrade performance