On Tuesday 08 Jul 2003 9:34 am, nolan@celery.tssi.com wrote:
> > So why am I getting substantially different times for identical queries
> > (except for parameter substitution)
>
> This sounds like the question I asked two weeks ago.
>
> What I concluded was that though they may be identical queries, they are
> not identical in terms of what happens when you execute them.
>
> Why? Because the tuples have been updated, meaning that the affected
> rows are in different physical locations than they used to be in both
> the table and in any indexes.
The "SELECT" part, which is the determining factor in all of this is reading
unchanged data from tables unchanged since a vacuum full/analyse. Besides,
the timings are consistent.
> Running a vacuum analyze and rebuilding indexes between runs should
> produce more consistent timings, if consistent timings are important.
>
> Readonly queries running on an otherwise idle server should produce
> more consistent timings, subject to caching issues.
Yep - which is why I'm puzzled. It's the readonly part of this that's taking
the extra time. The DELETE/INSERT are to another table - I'm summarising
activity logs into an hourly stats table.
--
Richard Huxton