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 <l...@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-boun...@postgis.refractions.net [mailto:
> postgis-users-boun...@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@postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>
_______________________________________________
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
