From: Michael Fuhr <mike@fuhr.org>
To: asif ali <asif_icrossing@yahoo.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Weird performance drop after VACUUM
Date: Fri, 26 Aug 2005 17:26:41 -0600
Message-ID: <20050826232641.GA19583@winnie.fuhr.org> (raw)
In-Reply-To: <20050826225224.27637.qmail@web35212.mail.mud.yahoo.com>
References: <1125093859.9329.30.camel@localhost.localdomain>
<20050826225224.27637.qmail@web35212.mail.mud.yahoo.com>
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
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: mike@fuhr.org, asif_icrossing@yahoo.com
Subject: Re: Weird performance drop after VACUUM
In-Reply-To: <20050826232641.GA19583@winnie.fuhr.org>
* 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