From: | Richard Huxton <dev(at)archonet(dot)com> |
---|---|
To: | nolan(at)celery(dot)tssi(dot)com |
Cc: | tgl(at)sss(dot)pgh(dot)pa(dot)us (Tom Lane), pgsql-general(at)postgresql(dot)org |
Subject: | Re: SQL Functions and plan time |
Date: | 2003-07-08 10:27:37 |
Message-ID: | 200307081127.37620.dev@archonet.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Tuesday 08 Jul 2003 9:34 am, nolan(at)celery(dot)tssi(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Dmitry Tkach | 2003-07-08 14:15:57 | Re: Backwards index scan |
Previous Message | Weiping He | 2003-07-08 09:47:34 | Re: any body using Solaris8 with postgresql 7.3.3 |