Skip site navigation (1) Skip section navigation (2)

Incorrect row estimates in plan?

From: pgdba <postgresql(at)inbox(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Incorrect row estimates in plan?
Date: 2007-09-26 14:22:45
Message-ID: (view raw or whole thread)
Lists: pgsql-performance
Hi, I am having some trouble understanding a plan and was wondering if anyone
could guide me. The query in question here seems to be showing some
incorrect row counts. I have vacuumed and analyzed the table, but the
estimate versus the actual total seems to be way out (est 2870 vs actual
85k). Perhaps I am reading the plan incorrectly though. (hopefully the plan
below is readable)

db=# select version();
PostgreSQL 8.2.4 on i486-pc-linux-gnu, compiled by GCC cc (GCC) 4.1.2
(prerelease) (Debian 4.1.1-21)

db=# show shared_buffers ;

#4GB ram, 2 SATA striped, XFS

db=# show default_statistics_target;

# stats have been raised to 1000 on both the destip and srcip columns
# create index slog_gri_idx on slog (gid,rule,(case when rule in (8,9) then
destip else srcip end)) WHERE (rule in (1, 2, 8, 9, 10));
# vacuum analyze verbose slog;

db=# show random_page_cost ;

db=# select count(*) from slog

db=#select  count(*) as total
from slog
where gid=10000::INTEGER
and rule in (1,2,8,9,10)
and (case when rule in (8,9) then destip else srcip

# problematic query
explain analyze
select  coalesce(uri,host((case when rule in (8,9) then srcip else destip
end))) as
        case when rule in (8,9) then 'ext' else 'int' end as tp,
        count(*) as total,
        coalesce(sum(destbytes),0)+coalesce(sum(srcbytes),0) as bytes
from slog
where gid=10000::INTEGER
and rule in (1,2,8,9,10)
and (case when rule in (8,9) then destip else srcip
group by destip,tp
order by bytes desc,total desc,destip limit 20

Limit  (cost=6490.18..6490.23 rows=20 width=61) (actual
time=2036.968..2037.220 rows=20 loops=1)
->  Sort  (cost=6490.18..6490.90 rows=288 width=61) (actual
time=2036.960..2037.027 rows=20 loops=1)
        Sort Key: (COALESCE(sum(destbytes), 0::numeric) +
COALESCE(sum(srcbytes), 0::numeric)), count(*), COALESCE(uri, host(CASE WHEN
(rule = ANY ('{8,9}'::integer[])) THEN srcip ELSE destip END))
        ->  HashAggregate  (cost=6470.50..6478.42 rows=288 width=61) (actual
time=2008.478..2022.125 rows=2057 loops=1)
            ->  Bitmap Heap Scan on slog  (cost=82.98..6434.62 rows=2870
width=61) (actual time=50.235..1237.948 rows=83538 loops=1)
                    Recheck Cond: ((gid = 10000) AND (rule = ANY
('{1,2,8,9,10}'::integer[])) AND (CASE WHEN (rule = ANY
('{8,9}'::integer[])) THEN destip ELSE srcip END = ''::inet))
                    ->  Bitmap Index Scan on slog_gri_idx  (cost=0.00..82.26
rows=2870 width=0) (actual time=41.306..41.306 rows=83538 loops=1)
                        Index Cond: ((gid = 10000) AND (rule = ANY
('{1,2,8,9,10}'::integer[])) AND (CASE WHEN (rule = ANY
('{8,9}'::integer[])) THEN destip ELSE srcip END = ''::inet))
Total runtime: 2037.585 ms

Does anyone have any suggestions?

View this message in context:
Sent from the PostgreSQL - performance mailing list archive at


pgsql-performance by date

Next:From: Tom LaneDate: 2007-09-26 14:45:14
Subject: Re: Incorrect row estimates in plan?
Previous:From: Bruce MomjianDate: 2007-09-26 08:21:12
Subject: Re: Effects of cascading references in foreign keys

Privacy Policy | About PostgreSQL
Copyright © 1996-2015 The PostgreSQL Global Development Group