At 11:31 AM 6/5/99 -0400, Tom Lane wrote:
>In short, if you load a bunch of tuples into a table, the first select
>after the load can run a lot slower than you might expect, because it'll
>be writing back most or all of the pages it touches. But that penalty
>doesn't affect every select, only the first one to scan a newly-written
>tuple.
While I don't doubt your analysis is correct for the case you've
uncovered, it doesn't explain why surrounding a bunch of selects
with a begin/end block greatly descreases disk activity for tables
that don't change. I'm pulling out "select" lists (html <select>)
from small tables of counties, states, countries for the project
I'm working on. The two countries, for instance, are "USA" and
"CA" and the table's not been updated in two months :). I'm
building a form and doing a very simple "select * from county_locales"
type selects, then building a <select> list containing all of the
possible values (not as many as you might think, this project
involves only the Pacific Northwest). There are several of
these selects executed for each form. Without the transaction
block, there's a lot of disk activity. With it, much less.
I can go pull out the begin/end blocks, they're conditionalized
in my Tcl scripts based on a "postgres" predicate so they'll
disappear if I migrate the database to another engine. Maybe
I'll have time this afternoon, if you'd like me to confirm, I'm
going to a brunch right now...
- Don Baccus, Portland OR <dhogaza@pacifier.com> Nature photos, on-line guides, and other goodies at
http://donb.photo.net