[postgis-users] No index usage on geography query plan?
Paragon Corporation
lr at pcorp.us
Thu May 27 23:31:03 PDT 2010
Okay I think the fix is a really simple one
Change your ST_Intersects function to this and see if it behaves right
CREATE OR REPLACE FUNCTION st_intersects(geography, geography)
RETURNS boolean AS
'SELECT $1 && $2 AND _ST_Distance($1, $2, 0.0, false) < 0.00001'
LANGUAGE 'sql' IMMUTABLE
COST 100;
It should no longer need the && help to use the index.
_____
From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of Nicholas
Bower
Sent: Thursday, May 27, 2010 6:24 PM
To: PostGIS Users Discussion
Subject: Re: [postgis-users] No index usage on geography query plan?
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 &&
'0103000020E61000000100000005000000F8C610001C305D40B24CBF44BC613FC0D6E253008
C385D40DE72F563935640C0DF1B430070AF5C40EE77280AF4654
0C01EFCC401F4B25C409F3BC1FEEB503FC0F8C610001C305D40B24CBF44BC613FC0'::geogra
phy)
Filter: st_intersects(border,
'0103000020E61000000100000005000000F8C610001C305D40B24CBF44BC613FC0D6E253008
C385D40DE72F563935640C0DF1B430070AF5C40EE7728
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
h <http://www.postgis.us> ttp://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,
'0103000020E61000000100000005000000F8C610001C305D40B24CBF44BC613FC0D6E253008
C385D40DE72F563935640C0DF1B430070AF5C40EE77280AF46540C01EFCC401F4B25C409F3BC
1FEEB503FC0F8C610001C305D40B24CBF44BC613FC0'::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/d98e163f/attachment.html>
More information about the postgis-users
mailing list