Re: Weird performance drop after VACUUM

Michael
The database is on the same system.
What I am doing is only "VACUUM analyze
conversion_table"

I did the the same thing on a newly created database.
And got the same result. So after "VACUUM analyze"
performance dropped.
Please see this. Runtime changes from "7755.115" to
"14859.291" ms

explain analyze
select keyword_id,sum(daily_impressions) as
daily_impressions ,
sum(daily_clicks) as daily_clicks,
COALESCE(sum(daily_cpc::double precision),0) as
daily_cpc, sum(daily_revenues)as daily_revenues,
sum(daily_actions)as daily_actions
,count(daily_cpc) as count from conversion_table c
where c.conversion_date BETWEEN '2005-06-07' and
'2005-08-17'
group by keyword_Id

"HashAggregate (cost=18686.51..18686.54 rows=2
width=52) (actual time=7585.827..7720.370 rows=55717
loops=1)"
" -> Index Scan using conversion_table_pk on
conversion_table c (cost=0.00..18599.25 rows=4986
width=52) (actual time=0.129..2882.066 rows=885493
loops=1)"
" Index Cond: ((conversion_date >=
'2005-06-07'::date) AND (conversion_date <=
'2005-08-17'::date))"
"Total runtime: 7755.115 ms"

VACUUM analyze conversion_table

explain analyze

select keyword_id,sum(daily_impressions) as
daily_impressions ,
sum(daily_clicks) as daily_clicks,
COALESCE(sum(daily_cpc::double precision),0) as
daily_cpc, sum(daily_revenues)as daily_revenues,
sum(daily_actions)as daily_actions
,count(daily_cpc) as count from conversion_table c
where c.conversion_date BETWEEN '2005-06-07' and
'2005-08-17'
group by keyword_Id

"GroupAggregate (cost=182521.76..200287.99 rows=20093
width=37) (actual time=8475.580..12618.793 rows=55717
loops=1)"
" -> Sort (cost=182521.76..184698.58 rows=870730
width=37) (actual time=8475.246..9418.068 rows=885493
loops=1)"
" Sort Key: keyword_id"
" -> Seq Scan on conversion_table c
(cost=0.00..27336.12 rows=870730 width=37) (actual
time=0.007..1520.788 rows=885493 loops=1)"
" Filter: ((conversion_date >=
'2005-06-07'::date) AND (conversion_date <=
'2005-08-17'::date))"
"Total runtime: 14859.291 ms"

--- Michael Fuhr <mike(at)fuhr(dot)org> wrote:

> On Fri, Aug 26, 2005 at 05:10:49PM -0700, asif ali
> wrote:
> > "GroupAggregate (cost=195623.66..206672.52
> rows=20132
> > width=16) (actual time=8205.283..10139.369
> rows=55291
> > loops=1)"
> > " -> Sort (cost=195623.66..198360.71
> rows=1094820
> > width=16) (actual time=8205.114..9029.501
> rows=863883
> > loops=1)"
> > " Sort Key: keyword_id"
> > " -> Seq Scan on keyword_conversion_table
> c
> > (cost=0.00..29990.83 rows=1094820 width=16)
> (actual
> > time=0.057..1422.319 rows=863883 loops=1)"
> > " Filter: ((conversion_date >=
> > '2005-06-07'::date) AND (conversion_date <=
> > '2005-08-17'::date))"
> > "Total runtime: 14683.617 ms"
>
> What are your effective_cache_size and work_mem
> (8.x) or sort_mem (7.x)
> settings? How much RAM does the machine have? If
> you have enough
> memory then raising those variables should result in
> better plans;
> you might also want to experiment with
> random_page_cost. Be careful
> not to set work_mem/sort_mem too high, though. See
> "Run-time
> Configuration" in the "Server Run-time Environment"
> chapter of the
> documentation for more information about these
> variables.
>
> --
> Michael Fuhr
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org
>

____________________________________________________
Start your day with Yahoo! - make it your home page
http://www.yahoo.com/r/hs