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

[Bborie Park]

I can't believe I completely missed the lack of index usage.

As for robe's concern regarding index usage, I know that indices are
used for my raster queries...

SELECT
	sum(sum) / sum(count) AS average
FROM (
	SELECT
		(ST_SummaryStats(ST_Clip(r.tile, 1, g.shape))).*
	FROM tops.tmean AS r
	CROSS JOIN (
		SELECT
			ST_GeomFromEWKB(SOME_CRAZY_EWKB) AS shape
	) AS g
	WHERE r.observation_date BETWEEN '2012-07-01' AND '2012-07-15'
		AND ST_Intersects(r.tile, g.shape)
) foo;

http://explain.depesz.com/s/feS

-bborie

On Tue, Aug 28, 2012 at 7:49 PM, Paragon Corporation <lr at pcorp.us> wrote:
> 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
>
>
> _______________________________________________
> postgis-devel mailing list
> postgis-devel at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-devel