Re: query has huge variance in execution times

On Wed, Mar 31, 2010 at 2:10 PM, Brian Cox <brian(dot)cox(at)ca(dot)com> wrote:

>
>
> so the question still remains: why did it take > 20 mins? To see if it was
> due to autovacuum running ANALYZE, I turned off autovacuum, created a table
> using SELECT * INTO temp FROM ts_stats_tranunit_user_daily, added the index
> on ts_interval_start_time and ran the prepared query with temp, but the
> query completed in a few secs.
>
> It's possible that statistics were updated between the >20 minute run and
your most recent prepared query test. In fact, comparing the plans between
your two emails, it's quite likely, as even the non-prepared versions are
not producing the same plan or the same estimates; it's therefore possible
that your problem has already corrected itself if you're unable to duplicate
the 20 minute behaviour at this point.

Taking a look at the statistics accuracy with an explain analyze might still
be informative, however.

--
- David T. Wilson
david(dot)t(dot)wilson(at)gmail(dot)com