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

[Paragon Corporation]

Jorge,

It doesn't look like your index is being used at all.  How many rows in your
raster table and what is the pixel width/height?

I'm assuming you are running 2.1.0SVN?  
Regarding call to intersects -- it should in theory only do it for the tiles
that satisfy the && requirement.  I always thought that used to show
St_Convexhull in the plan though I could be mistaken.  So I'm a bit
concerned there might be something amiss with our index functional resolve.


Regina 

-----Original Message-----
From: postgis-devel-bounces at postgis.refractions.net
[mailto:postgis-devel-bounces at postgis.refractions.net] On Behalf Of
dustymugs
Sent: Tuesday, August 28, 2012 6:49 PM
To: postgis-devel at postgis.refractions.net
Subject: Re: [postgis-devel] How could I make this query run faster?

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 &&
> '0103000020AD1000000100000005000000511BE8B4018057C0511BE8B401E05040511
> BE8B4014057C0511BE8B401E05040511BE8B4014057C0511BE8B401C05040511BE8B40
> 18057C0511BE8B401C05040511BE8B4018057C0511BE8B401E05040'::geometry)
> AND 
> _st_intersects('0103000020AD1000000100000005000000511BE8B4018057C0511B
> E8B401E05040511BE8B4014057C0511BE8B401E05040511BE8B4014057C0511BE8B401
> C05040511BE8B4018057C0511BE8B401C05040511BE8B4018057C0511BE8B401E05040
> '::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,
> 
_______________________________________________
postgis-devel mailing list
postgis-devel at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-devel