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

[Paragon Corporation]

Darrel,
> I have a table of 11 rasters (3601x3134, originally tifs) 
> each tiled into 1000x1000 blocks and I have a simple geometry 
> shape file. My first ever SQL query looks like this:
> 
> SELECT rid, gid, ST_Value(rast, 1, geom), filename, 
> ST_X(geom) As X_coord, ST_Y(geom) As Y_coord FROM 
> public.rasters, public.mypoints As A WHERE 
> ST_Intersects(rasters.rast,1, A.geom) ORDER BY rid ASC ;
> 
> as I am interested in drawing cross-sections of the 'height' 
> data contained within the grids.  Each intersect draws data 
> from only one tile.
> 
> Now the query works as intended (apologies if it can be done 
> better) but the timings are:
> 
> 1) ~35s
> 2) ~235s
> (n.b. these are the best timings - I did try different tiling 
> sizes but both smaller and larger took longer)
> 
> Now this result baffles me as I expected the 2600k to be much 
> closer as watching a CPU monitor the code does not seem to be 
> heavily multithreaded.
> 
> I would be grateful for any suggestions as to why the 2600k 
> is performing badly in comparison (and of course I would 
> welcome any suggestions that might speed up the result - 
> noting that ultimately this extraction could have as many as 
> 250,000 rasters to work on.
> 
> Thanks
> 
> Darrel
> 
> ps sorry for asking questions at the weekend but this is the 
> only time I get to work on this stuff!

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.

Regarding the 64-bit issue -- I think even with that there are still issues
with upping shared memory
on Windows which haven't been resolved.  So even on 32-bit we've never been
successful upping it more than say 640 MB without it crashing
at some point.

The benefit I do see with 64-bit possibly is with the work_mem which would
be more useful for raster than anything else.

It's hard to say how good or bad the performance is though without going
thru the exercise.

Hope that helps,
Regina
http://www.postgis.us