On Wed, Mar 31, 2010 at 2:10 PM, Brian Cox
<brian.cox@ca.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.t.wilson@gmail.com