Date: Fri, 26 Aug 2005 17:26:41 -0600
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
Message-ID: <20050826232641.GA19583@winnie.fuhr.org>
References: <1125093859.9329.30.camel@localhost.localdomain>
	<20050826225224.27637.qmail@web35212.mail.mud.yahoo.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <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