[postgis-devel] ST_Value performance

[Sandro Santilli]

I'm willing to dump the contents of a raster
to a table to get:

 x,y,point,value1,value2,valueN

Thats for an N band raster.

After first using ST_PixelAsPolygon call, which was relatively fast
I ended up with 206893 rows in a new table, one per pixel.

Now I'm running an update adding the value to each of those rows,
using ST_Value. I do understand that over 200k calls are a lot to 
do, but fetching a value itself shouldn't take long, right ?
It's a 661x313 pixels raster.

Well... it's been running at 100% CPU for a good amount of time now.

The query:

=# update chenyx06a_pixels set shift_lat = ST_Value(o.rast, 1, st_centroid) from chenyx06a o;

The time it is taking:

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
11518 postgres  20   0  309m  51m  42m R  100  1.3  36:55.82 postgres

That's almost 40 minutes with CPU at 100%.
Is it expected ?

--strk; 

  ,------o-. 
  |   __/  |    Thank you for PostGIS-2.0 Topology !
  |  / 2.0 |    http://www.pledgebank.com/postgistopology
  `-o------'