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
Date: Fri, 26 Aug 2005 16:13:56 -0700 (PDT)
Message-ID: <20050826161119.P22700@wolf.pjkh.com> (raw)
In-Reply-To: <20050826225224.27637.qmail@web35212.mail.mud.yahoo.com>
References: <20050826225224.27637.qmail@web35212.mail.mud.yahoo.com>
> 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
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: postgresql@philip.pjkh.com, asif_icrossing@yahoo.com
Subject: Re: Weird performance drop after VACUUM
In-Reply-To: <20050826161119.P22700@wolf.pjkh.com>
* 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