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

[Bborie Park]

Sigh.  Same query but with actual EXPLAIN ANALYZE...

http://explain.depesz.com/s/2GH

On Tue, Aug 28, 2012 at 9:45 PM, Bborie Park <dustymugs at gmail.com> wrote:
> 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