[postgis-users] Extent on multiple tables

[Mark Cave-Ayland]

Hi Markus,

> -----Original Message-----
> From: postgis-users-bounces at postgis.refractions.net 
> [mailto:postgis-users-bounces at postgis.refractions.net] On 
> Behalf Of Markus Schaber
> Sent: 21 October 2005 15:09
> To: PostGIS Users Discussion
> Subject: Re: [postgis-users] Extent on multiple tables

(cut)

> I will oppose this.
> 
> AFAIR, they are loaded row-by-row, and feed into the 
> estimated_extend aggregator which changes his internal state. 
> Once processed by the aggregate, they'll be thrown away as 
> they're not needed any more, as well as the shared buffers 
> are recycled.
> 
> My local test here with a 35 million row table seems to prove 
> this, as the postmaster has a constant memory usage of 17M 
> during the whole sequential table scan.

Having checked the docs, I now remember that estimated_extent() is not
actually an aggregate but a function, and so an actual query should look
like this:

select extent(g) from (
	select estimated_extent('', 'table1', 'the_geom') AS g
	union all
	select estimated_extent('', 'table2', 'the_geom') AS g
) as foo;

For estimated_extent() the values are read directly from the statistics
collected during ANALYZE and so we know for a fact that this won't visit any
tuples.

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.

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?


Kind regards,

Mark.

------------------------
WebBased Ltd
17 Research Way
Plymouth
PL6 8BT

T: +44 (0)1752 797131
F: +44 (0)1752 791023

http://www.webbased.co.uk   
http://www.infomapper.com 
http://www.swtc.co.uk  

This email and any attachments are confidential to the intended recipient
and may also be privileged. If you are not the intended recipient please
delete it from your system and notify the sender. You should not copy it or
use it for any purpose nor disclose or distribute its contents to any other
person.