From: asif ali <asif_icrossing@yahoo.com>
To: Michael Fuhr <mike@fuhr.org>
Cc: pgsql-performance@postgresql.org
Subject: Re: Weird performance drop after VACUUM
Date: Mon, 29 Aug 2005 15:59:12 -0700 (PDT)
Message-ID: <20050829225912.69965.qmail@web35204.mail.mud.yahoo.com> (raw)
In-Reply-To: <20050829202856.GA99478@winnie.fuhr.org>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: asif_icrossing@yahoo.com, mike@fuhr.org
Subject: Re: Weird performance drop after VACUUM
In-Reply-To: <20050829225912.69965.qmail@web35204.mail.mud.yahoo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox