On 07/12/2011 02:51 PM, Kevin Grittner wrote:
I ran x a bunch of times to get a baseline, then y once, then x a
bunch more times. The results were a bit surprising:
cir=> \timing
Timing is on.
cir=> execute x('000000000000001','2011-6-30');
count
-------
3456
(1 row)
Time: 9.823 ms
cir=> execute x('000000000000001','2011-6-30');
count
-------
3456
(1 row)
Time: 8.481 ms
cir=> execute x('000000000000001','2011-6-30');
count
-------
3456
(1 row)
Time: 14.054 ms
cir=> execute x('000000000000001','2011-6-30');
count
-------
3456
(1 row)
Time: 10.169 ms
cir=> execute y('000000000000001', '000000000000001','2011-6-30');
UPDATE 3456
Time: 404.244 ms
cir=> execute x('000000000000001','2011-6-30');
count
-------
3456
(1 row)
Time: 128.643 ms
cir=> execute x('000000000000001','2011-6-30');
count
-------
3456
(1 row)
Time: 2.657 ms
cir=> execute x('000000000000001','2011-6-30');
count
-------
3456
(1 row)
Time: 5.883 ms
cir=> execute x('000000000000001','2011-6-30');
count
-------
3456
(1 row)
Time: 2.645 ms
cir=> execute x('000000000000001','2011-6-30');
count
-------
3456
(1 row)
Time: 2.753 ms
cir=> execute x('000000000000001','2011-6-30');
count
-------
3456
(1 row)
Time: 2.253 ms
Interesting. When you did you test, did you also find WAL write activity
when running x the first time after y?
(It's very hard to catch in only a single query, though).
Running the update made the next SELECT slow, then it was much
*faster*. My best guess is that the data landed in a more
concentrated set of pages after the update, and once autovacuum
kicked in and cleaned things up it was able to get to that set of
data faster.
autovacuum | off
Well, certainly not while under modification without running
autovacuum. That's disabling an integral part of what keeps
performance up.
Oh, it's just switched off for testing, so that I can control when
vacuum runs and make sure that it's not
skewing the results while I am measuring something.
In a real database I would probably err on vacuuming more than less.
For a fully cached database I would probably want to switch off HOT
pruning and compaction (which from what we see
is done synchronously with the select) and leave it up to the
asynchronous auto vacuum to do that. But maybe I am
still not quite understanding the performance implications.
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
