[postgis-users] Extent on multiple tables

[Markus Schaber]

Hi, Mark,

Mark Cave-Ayland wrote:

> As for the extent() case, I really can't say either way. In PostgreSQL, a
> page must be loaded into a buffer before it can be accessed (it can't be
> read directly from the disk), so either way all of the tuples must be
> visited in order to produce the final value. I guess it all depends on the
> buffer replacement policy (LRU) as to how useful the contents of shared
> buffers will be after the final aggregate is processed.

AFAIR, PostgreSQL 8.0 introduced a new replacement policy for the shared
buffers that will handle such "sequential scan over a large table" cases
much better than LRU, which was used until 7.4.

> The only part I'm confused about is 17M you see on your aggregate - could it
> be that this is just the memory used by the process, and doesn't include
> shared memory?

I had about 24M virtual, 17M resident and 15M shared, according to top.
But those values are specific to your setup and config, so you may have
rather different values there.

But the fact that those values newer grew during the query is a proof
that PostgreSQL does not keep all geometries in memory until completion
of the aggregate query, because for a 35 million row table, this would
accumulate to 1/2 byte per geometry :-)

Markus