Re: Weird performance drop after VACUUM

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