[postgis-users] Rv: Postgis for Postgres 64 bits

[Paragon Corporation]

> "First thing 1000x1000 blocks is too big.  If you are going 
> to be doing a lot of ST_Value calls, you probably want to 
> chunk your tiles to 100x100 or below.
> 
> ST_Value currently requires doing a memcopy for each call so 
> the bigger your tiles the more memcopying it will have to do.
> 
> On top of that the bigger your tiles, the less useful 
> ST_Intersects is.  Its kind of the same issue with big 
> geometries -- like if you stuffed all of africa in one 
> record, and then were searching for a city in Africa, the 
> ST_Intersects will not be that useful."
> 
> Thanks for this. I did do a few tests. This is what I got:
> 
> Tile Size	Time
> 2000		350873ms
> 1000		235415
> 500		288298
> 300		234895	
> 200		231374
> 
> I guess I was concerned that at 200x200 the images were split 
> across 3344 rows as opposed to the 176 of the 1000x1000.  I 
> am learning here, so I will give 100x100 a go (tomorrow) - I 
> just worry about when I need to put the images back together 
> (images will be uploaded as tiles and then the originals - 
> model outputs - will be deleted). The geometry is composed of 
> 100 points (this is for testing only - final configuration 
> has yet to be determined) -  I figured it would be faster if 
> they were all on the same tile, but may be not?
> 
> Thanks
> 
> Darrel
> 

Bborie probably has more info on this since he's working it and can
hopefully chime in.  Right now the ST_Union you'd use in the db
is not very efficient since its using plpgsql. Doing it with the
gdal_translate is a lot faster and you can do basic where
conditions with that like where file_name='...'.

 In 2.0 ST_Union will be done mostly in C so should be faster.

As far as reconstituting, yes and no.  If your tiles are smaller and you
don't need the full tile,
doing clip operations and union subset that covers a geometric region will
be faster again because of the whole memcopy issue.

However if you need to export the tile -- reconsitute the original, then you
are right a bigger tile would be better, though you may
want to try gdal_translate as that I think is much more efficient for
export.
http://www.postgis.org/documentation/manual-svn/RT_FAQ.html#id2997931

A lot fo these performance issues we'll be dealing with in the 2.1 release,
but for 2.0, we are just focussing on functionality and stability.

Hope that helps,
Regina