On Tue, 2007-03-06 at 18:47 +0000, Heikki Linnakangas wrote:
> Tom Lane wrote:
> > Jeff Davis <[EMAIL PROTECTED]> writes:
> >> If I were to implement this idea, I think Heikki's bitmap of pages
> >> already read is the way to go.
> > I think that's a good way to guarantee that you'll not finish in time
> > for 8.3. Heikki's idea is just at the handwaving stage at this point,
> > and I'm not even convinced that it will offer any win. (Pages in
> > cache will be picked up by a seqscan already.)
> The scenario that I'm worried about is that you have a table that's
> slightly larger than RAM. If you issue many seqscans on that table, one
> at a time, every seqscan will have to read the whole table from disk,
> even though say 90% of it is in cache when the scan starts.
If you're issuing sequential scans one at a time, that 90% of the table
that was cached is probably not cached any more, unless the scans are
close together in time without overlapping (serial sequential scans).
And the problem you describe is no worse than current behavior, where
you have exactly the same problem.
> This can be alleviated by using a large enough sync_scan_offset, but a
> single setting like that is tricky to tune, especially if your workload
> is not completely constant. Tune it too low, and you don't get much
> benefit, tune it too high and your scans diverge and you lose all benefit.
I see why you don't want to manually tune this setting, however it's
really not that tricky. You can be quite conservative and still use a
good fraction of your physical memory. I will come up with some numbers
and see how much we have to gain.
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings