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=P7dsyrW1AuZ5qo9mOCrfFeNwY6D2KAGORCVLijqlE6PtMA/xMq3bUKzpNz5ni2nEwdF+Zhok+ZsDNQe3UwjVxWzgtFl08TkSaoW75j+zrjr7o3YHsJ84PYmWpLtmOg7dsEzGJoy/FKw/PuztpReEZQWXWN3VY69cGeLkmWUntyM=
	;
Message-ID: <20050827001049.58959.qmail@web35210.mail.mud.yahoo.com>
Date: Fri, 26 Aug 2005 17:10:49 -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: <20050826232641.GA19583@winnie.fuhr.org>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: 8bit

Thanks Michael For your reply.

Here is performance on the database on which i did 
VACUUM ANALYZE

explain analyze
select   keyword_id
	,sum(daily_impressions) as daily_impressions 
	,sum(daily_actions)as daily_actions 
 from  conversion_table c where    c.conversion_date
BETWEEN '2005-06-07' and '2005-08-17' 
	group by keyword_Id 

"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"


Now see if am changing the query and commenting one
column.

explain analyze
select   keyword_id
	,sum(daily_impressions) as daily_impressions 
--	,sum(daily_actions)as daily_actions 
 from  conversion_table c where    c.conversion_date
BETWEEN '2005-06-07' and '2005-08-17' 
	group by keyword_Id 


"HashAggregate  (cost=27373.51..27373.52 rows=2
width=16) (actual time=3030.386..3127.073 rows=55717
loops=1)"
"  ->  Seq Scan on conversion_table c 
(cost=0.00..27336.12 rows=4986 width=16) (actual
time=0.050..1357.164 rows=885493 loops=1)"
"        Filter: ((conversion_date >=
'2005-06-07'::date) AND (conversion_date <=
'2005-08-17'::date))"
"Total runtime: 3159.162 ms"


I noticed "GroupAggregate" changes to "HashAggregate"
and performance from 14 sec to 3 sec.


On the other hand I have another database which I did
not do "VACUUM ANALYZE"  working fine.


explain analyze
select   keyword_id
	,sum(daily_impressions) as daily_impressions 
	,sum(daily_actions)as daily_actions 
 from  conversion_table c where    c.conversion_date
BETWEEN '2005-06-07' and '2005-08-17' 
	group by keyword_Id 


"HashAggregate  (cost=27373.51..27373.52 rows=2
width=16) (actual time=3024.289..3120.324 rows=55717
loops=1)"
"  ->  Seq Scan on conversion_table c 
(cost=0.00..27336.12 rows=4986 width=16) (actual
time=0.047..1352.212 rows=885493 loops=1)"
"        Filter: ((conversion_date >=
'2005-06-07'::date) AND (conversion_date <=
'2005-08-17'::date))"
"Total runtime: 3152.437 ms"


I am new to postgres. Thanks in advance.


asif ali


--- Michael Fuhr <mike@fuhr.org> wrote:

> On Fri, Aug 26, 2005 at 03:52:24PM -0700, asif ali
> wrote:
> > I have the same issue. After doing "VACCUME
> ANALYZE"
> > performance of the query dropped. 
> 
> Your EXPLAIN output doesn't show the actual query
> times -- could
> you post the EXPLAIN ANALYZE output?  That'll also
> show how accurate
> the planner's row count estimates are.
> 
> > Before "VACCUME ANALYZE"
> > 
> > "Index Scan using conversion_table_pk on
> > keyword_conversion_table c  (cost=0.00..18599.25
> > rows=4986 width=95)"
> > "  Index Cond: ((conversion_date >=
> > '2005-06-07'::date) AND (conversion_date <=
> > '2005-08-17'::date))"
> > 
> > After  "VACCUME ANALYZE"
> > 
> > "Seq Scan on conversion_table c 
> (cost=0.00..29990.83
> > rows=1094820 width=66)"
> > "  Filter: ((conversion_date >=
> '2005-06-07'::date)
> > AND (conversion_date <= '2005-08-17'::date))"
> > 
> > I dont know why system is doing "Seq scan" now.
> 
> Notice the row count estimates: 4986 in the "before"
> query and
> 1094820 in the "after" query.  In the latter, the
> planner thinks
> it has to fetch so much of the table that a
> sequential scan would
> be faster than an index scan.  You can see whether
> that guess is
> correct by disabling enable_seqscan to force an
> index scan.  It
> might be useful to see the output of the following:
> 
> SET enable_seqscan TO on;
> SET enable_indexscan TO off;
> EXPLAIN ANALYZE SELECT ...;
> 
> SET enable_seqscan TO off;
> SET enable_indexscan TO on;
> EXPLAIN ANALYZE SELECT ...;
> 
> You might also experiment with planner variables
> like effective_cache_size
> and random_page_cost to see how changing them
> affects the query
> plan.  However, be careful of tuning the system
> based on one query:
> make sure adjustments result in reasonable plans for
> many different
> queries.
> 
> -- 
> 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