What's a good way to improve this query? - Mailing list pgsql-general

Hello,

I'm running this PostGIS Raster query

select
st_scalex(rast),
st_scaley(rast),
st_skewx(rast),
st_skewy(rast),
st_width(rast),
st_height(rast),
rid,
st_upperleftx(rast),
st_upperlefty(rast),
st_numbands(rast)
from
my_postgis_raster_table



I want to remark that, even when 'rast' is a complex type and can be really big, I'm getting just metadata. Not the
whole'rast' column. Anyway, the average dimensions of a 'rast' column in like 600x400 pixels (8 bits per pixel). So,
notso big (about 234 KB per rast object).  

My table has 1257 rows, and this query takes about 45 secs to execute (45646 msecs). I think it's too slow. I'm just
gettingmetadata, not the whole 'rast' object, as said.  

This is the explain analyze output

Seq Scan on my_postgis_raster_table (cost=0.00..198.85 rows=1257 width=36) (actual time=86.867..51861.495 rows=1257
loops=1)
Total runtime: 51863.919 ms



So, basically a sequential scan. As expected, I guess (I'm not a postgres expert, so sorry if I'm talking nonsense)

I've calculated the effective transfer rate for this table

SELECT pg_size_pretty(CAST(pg_relation_size('my_postgis_raster_table') / 45646 * 1000 as int8)) AS bytes_per_second;

As it's 27KB. Isn't it a slow rate? Is there any kind of index I could create to speed this query? Maybe use some kind
ofcache system? 

Many thanks in advance,

--
Jorge Arevalo
Freelance developer

http://www.krop.com/jorgearevalo
http://about.me/jorgeas80

Enviado con Sparrow (http://www.sparrowmailapp.com/?sig)