DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com;
	h=Message-ID:Received:Date:From:Subject:To:Cc:In-Reply-To:MIME-Version:Content-Type:Content-Transfer-Encoding;
	b=HLoGm6F3K510fyFbvU5ytEh4st9qXXXpmOc7A00iKpZVX6lr2lXhHIWwYiVNPsTh2vFahLb2bvchJusoKMfOGCAQ10CNuJLopP3/Uq0Wx7gFu7I6QWYqeTgWyjbxfsqC5In4GKuE0JDpu9NqP0SrZEApRx0ToblHoknYkjLGWZs=
	;
Message-ID: <20050829180717.74297.qmail@web35207.mail.mud.yahoo.com>
Date: Mon, 29 Aug 2005 11:07:17 -0700 (PDT)
From: asif ali <asif_icrossing@yahoo.com>
Subject: Re: Weird performance drop after VACUUM
To: Michael Fuhr <mike@fuhr.org>
Cc: pgsql-performance@postgresql.org
In-Reply-To: <20050827014126.GA33637@winnie.fuhr.org>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: 8bit

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@fuhr.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