Re: Weird performance drop after VACUUM

> Hi,
> I have the same issue. After doing "VACCUME ANALYZE"
> performance of the query dropped.
>
> Here is the query
> explain select * from conversion_table c where
> c.conversion_date BETWEEN '2005-06-07' and
> '2005-08-17'
>
> 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.

I could be wrong as I'm definitely no expert on reading the output of
EXPLAIN, but it seems to say that prior to VACUUM it was expecting to
retrieve 4986 rows and afterwards expecting to retrieve 1094820 rows.

Which is a pretty big difference.

So maybe the statistics were just really really off prior to vacuuming and
once it did vacuum it realized there would be a lot more matches and since
there were a lot more matches the planner decided to do a seq scan since
it would be quicker overall...

Maybe? Seems I've heard Tom Lane say something to that affect, although
much more eloquently :-)

-philip