[postgis-users] No index usage on geography query plan?

[Nicholas Bower]

Right you are.

wastac=> explain analyze select count(*) from wastac.t_tile_geometry where
border && ST_GeographyFromText('SRID=4326;POLYGON((116.751709
-31.381779,116.883545 -32.676373,114.741211 -32.796510,114.796143
-31.316101,116.751709 -31.381779))')
wastac->  AND ST_Intersects(border,
ST_GeographyFromText('SRID=4326;POLYGON((116.751709 -31.381779,116.883545
-32.676373,114.741211 -32.796510,114.796143-31.316101,116.751709
-31.381779))'));

 Aggregate  (cost=30.48..30.49 rows=1 width=0) (actual time=11.459..11.459
rows=1 loops=1)   ->  Index Scan using t_tile_geometry_border_key on
t_tile_geometry  (cost=0.00..30.47 rows=2 width=0) (actual
time=0.697..11.416 rows=95 loops=1)
         Index Cond: (border &&
'0103000020E61000000100000005000000F8C610001C305D40B24CBF44BC613FC0D6E253008C385D40DE72F563935640C0DF1B430070AF5C40EE77280AF4654
0C01EFCC401F4B25C409F3BC1FEEB503FC0F8C610001C305D40B24CBF44BC613FC0'::geography)
         Filter: st_intersects(border,
'0103000020E61000000100000005000000F8C610001C305D40B24CBF44BC613FC0D6E253008C385D40DE72F563935640C0DF1B430070AF5C40EE7728
0AF46540C01EFCC401F4B25C409F3BC1FEEB503FC0F8C610001C305D40B24CBF44BC613FC0'::geography)
 Total runtime: 11.537 ms
(5 rows)

In case it's relevant this was how I created the geography column from a
geometry one - I noticed the Polygon "restriction" had to be added in the
last statement (ie you don't get it for free from the cast in the first
statement).

create table wastac.t_tile_geometry as select *,
geography(ST_SetSRID(border, 4326)) as border_new,
geography(ST_SetSRID(centre, 4326)) as centre_new from
wastac.t_tile_geometry;

alter table wastac.t_tile_geometry_new drop column border;

alter table wastac.t_tile_geometry_new rename column border_new to border;

alter table wastac.t_tile_geometry alter column border set data type
geography(Polygon,4326);





On 27 May 2010 15:42, Paragon Corporation <lr at pcorp.us> wrote:

>  Nick,
> Okay we are seeing the same issue with our fastfoods data even with smaller
> windows.  I think the clue is  the plan here.
>
> The ST_Intersects geography function seems to be treated as a
> blackbox rather than a transparent function composed of && and _ST_Distance
>
> The magic of the geometry ST_Intersects is that the query plan can see into
> the function and decomposes it into
> && + _ST_Intersects
>
> For some reason, that's just not happening with geography.  Maybe Paul or
> Mark have a clue why that is.
>
> So I bet this uses an index and is much faster
> select count(*) from wastac.t_tile_geometry where
> border && ST_GeographyFromText('SRID=4326;POLYGON((116.751709
> -31.381779,116.883545 -32.676373,114.741211 -32.796510,114.796143
> -31.316101,116.751709 -31.381779))') AND ST_Intersects(border,
> ST_GeographyFromText('SRID=4326;POLYGON((116.751709 -31.381779,116.883545
> -32.676373,114.741211 -32.796510,114.796143-31.316101,116.751709
> -31.381779))'));
>
>  Thanks
> Regina  and Leo
> http://www.postgis.us
>  ------------------------------
> *From:* postgis-users-bounces at postgis.refractions.net [mailto:
> postgis-users-bounces at postgis.refractions.net] *On Behalf Of *Nicholas
> Bower
> *Sent:* Wednesday, May 26, 2010 6:42 PM
>
> *To:* PostGIS Users Discussion
> *Subject:* Re: [postgis-users] No index usage on geography query plan?
>
>
>
>> That does look like a pretty huge bounding polygon, but the geography we
>>
>> agree should still be using the spatial index, so probably making the
>> index
>> cost higher than it should
>>
>
> Fyi the border values are are simply composed of a regular 20km grid of
> ajoining polygon squares covering Australia (about 1.3M tiles).
>  Incidentally, with the border of tiles being square, the bounding box *is*
> the shape I guess.  It's not relevant, but the query returns a count 95 of
> these 20km tiles.
>
> We're dealing with satellite data - the datasets themselves have far bigger
> coverages (different table entirely), but this is why you've noticed the
> "region of interest" in the query is large.
>
>
>> Nick,
>>
>> Can you just for contrast, try to force it to use the index by doing
>>
>> set enable_seqscan = off;
>>
>>  explain analyze select count(*) from wastac.t_tile_geometry where
>> ST_Intersects(border, ST_GeographyFromText('SRID=4326;POLYGON((116.751709
>> -31.381779,116.883545 -32.676373,114.741211 -32.796510,114.796143
>> -31.316101,116.751709 -31.381779))'));
>>
>
> Strange - no difference.
>
>  wastac=> set enable_seqscan = off;
> SET
> wastac=> explain analyze select count(*) from wastac.t_tile_geometry where
> ST_Intersects(border, ST_GeographyFromText('SRID=4326;POLYGON((116.751709
> -31.381779,116.883545 -32.676373,114.741211
> -32.796510,114.796143-31.316101,116.751709 -31.381779))'));
>
>                                             QUERY PLAN
>
>
>
> --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>  Aggregate  (cost=10000364056.80..10000364056.81 rows=1 width=0) (actual
> time=8909.585..8909.586 rows=1 loops=1)
>    ->  Seq Scan on t_tile_geometry  (cost=10000000000.00..10000362993.14
> rows=425463 width=0) (actual time=2028.827..8909.533 rows=95 loops=1)
>          Filter: st_intersects(border,
> '0103000020E61000000100000005000000F8C610001C305D40B24CBF44BC613FC0D6E253008C385D40DE72F563935640C0DF1B430070AF5C40EE77280AF46540C01EFCC401F4B25C409F3BC1FEEB503FC0F8C610001C305D40B24CBF44BC613FC0'::geography)
>  Total runtime: 8909.626 ms
> (4 rows)
>
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20100528/76e3ca18/attachment.html>