Date: Fri, 26 Aug 2005 16:13:56 -0700 (PDT)
From: Philip Hallstrom <postgresql@philip.pjkh.com>
To: asif ali <asif_icrossing@yahoo.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Weird performance drop after VACUUM
In-Reply-To: <20050826225224.27637.qmail@web35212.mail.mud.yahoo.com>
Message-ID: <20050826161119.P22700@wolf.pjkh.com>
References: <20050826225224.27637.qmail@web35212.mail.mud.yahoo.com>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII; format=flowed

> 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