[postgis-devel] How could I make this query run faster?

[dustymugs]

The following might help (can't be certain) as each of the attribute
function calls (e.g. ST_Width, ST_ScaleX, ST_BandPixelType) passes the
raster back and forth.

SELECT
	st_band(rast, 1),
	(rm).*,
	(bm).*
FROM (
SELECT
	rast,
	ST_Metadata(rast) AS rm,
	ST_BandMetadata(rast, 1) AS bm
FROM public.elevation WHERE st_intersects(rast,
st_polygonfromtext('POLYGON((-94.00010416666670210
67.50010416666670210, -93.00010416666670210 67.50010416666670210,
-93.00010416666670210 67.00010416666670210, -94.00010416666670210
67.00010416666670210, -94.00010416666670210 67.50010416666670210))',
4269))
) foo

One thing that may slow things down is the conversion of the polygon to
a raster.  If that conversion is done only once, that isn't a problem
but I'm guessing that the conversion is done for every call to
st_intersects with each raster tile.  You may want to cache the
rasterized polygon for use...

-bborie

On 08/28/2012 03:28 PM, Jorge Arevalo wrote:
> SELECT st_band(rast, 1), st_width(rast), st_height(rast),
> st_bandpixeltype(rast, 1), st_bandnodatavalue(rast, 1),
> st_scalex(rast), st_scaley(rast), st_upperleftx(rast),
> st_upperlefty(rast) FROM public.elevation WHERE st_intersects(rast,
> st_polygonfromtext('POLYGON((-94.00010416666670210
> 67.50010416666670210, -93.00010416666670210 67.50010416666670210,
> -93.00010416666670210 67.00010416666670210, -94.00010416666670210
> 67.00010416666670210, -94.00010416666670210 67.50010416666670210))',
> 4269))
> 
> There's a GIST index created over rast column, as usual:
> 
> CREATE INDEX elevation_rast_gist
>   ON elevation
>   USING gist
>   (st_convexhull(rast) );
> 
> This is the 'explain analyze' output
> Seq Scan on elevation  (cost=0.00..6.30 rows=1 width=32) (actual
> time=2039.622..2614.787 rows=2 loops=1)
>    Filter: (((rast)::geometry &&
> '0103000020AD1000000100000005000000511BE8B4018057C0511BE8B401E05040511BE8B4014057C0511BE8B401E05040511BE8B4014057C0511BE8B401C05040511BE8B4018057C0511BE8B401C05040511BE8B4018057C0511BE8B401E05040'::geometry)
> AND _st_intersects('0103000020AD1000000100000005000000511BE8B4018057C0511BE8B401E05040511BE8B4014057C0511BE8B401E05040511BE8B4014057C0511BE8B401C05040511BE8B4018057C0511BE8B401C05040511BE8B4018057C0511BE8B401E05040'::geometry,
> rast, NULL::integer))
>  Total runtime: 2615.145 ms
> 
> The coordinates used in intersection are not important. The point is I
> want to fetch all raster rows that intersect a given window. So:
> 
> 1) Is there any other additional index I should create?
> 2) Is there any other way to write the query to make it faster?
> 
> Many thanks in advance, and best regards,
>