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=JTicJ7aGs+Zeadh2ZMP3406znto2Jl38AgbZXtcRQgDr19CWTDZQIk7Vz/aYQsU0H6zvuFO1XJzmrIxnUw+qQJkzNhdLiSwP3kSdVjefA2vq76vPcH88TNafqoVwSN/k6TbAJLNunR2aUz0ZPKtUOQelJa0t1s+MrnJf5VGX3OY=
	;
Message-ID: <20050829225912.69965.qmail@web35204.mail.mud.yahoo.com>
Date: Mon, 29 Aug 2005 15:59:12 -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: <20050829202856.GA99478@winnie.fuhr.org>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: 8bit

Michael,
The 
effective_cache_size, random_page_cost, work_mem
were set to default. (commented).
I have changed the setting of these and now the
performance is better see below.

"HashAggregate  (cost=42573.89..42925.52 rows=20093
width=37) (actual time=5273.984..5430.733 rows=55717
loops=1)"
"  ->  Seq Scan on keyword_conversion_table c 
(cost=0.00..27336.12 rows=870730 width=37) (actual
time=0.052..1405.576 rows=885493 loops=1)"
"        Filter: ((conversion_date >=
'2005-06-07'::date) AND (conversion_date <=
'2005-08-17'::date))"
"Total runtime: 5463.764 ms"


Thanks a lot


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

> On Mon, Aug 29, 2005 at 11:07:17AM -0700, asif ali
> wrote:
> > 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
> 
> As has been pointed out a couple of times, you're
> getting a different
> plan after VACUUM ANALYZE because the row count
> estimates are more
> accurate.  Unfortunately the more accurate estimates
> result in a
> query plan that's slower than the plan for the less
> accurate
> estimates.  PostgreSQL *thinks* the plan will be
> faster but your
> results show that it isn't, so you might need to
> adjust some of the
> planner's cost constants.
> 
> A asked some questions that you didn't answer, so
> I'll ask them again:
> 
> What's your effective_cache_size setting?
> What's your work_mem (8.x) or sort_mem (7.x)
> setting?
> What's your random_page_cost setting?
> How much available RAM does the machine have?
> What version of PostgreSQL are you running?
> 
> Various tuning guides give advice on how to set the
> above and other
> configuration variables.  Here's one such guide:
> 
> http://www.powerpostgresql.com/PerfList/
> 
> -- 
> Michael Fuhr
> 
> ---------------------------(end of
> broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
> 
>                http://www.postgresql.org/docs/faq
> 


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