Actually it's slower because the calculations require lots of transcendental math. Anyhow, it's slower. If anyone wants a quote on speed improvements, I'm happy to provide one, I have some good ideas about how to speed things up with some better data structures and caching.
P. On Mon, May 31, 2010 at 2:48 PM, Paragon Corporation <l...@pcorp.us> wrote: > Nicholas, > > I fear that may be the way it is. The intersects functionality of geography > is slower than intersects of geometry because it piggy backs on the distance > function rather than using an intersection matrix. > > With the index scan you are left with 10,347 records to check via the slower > distance function. > > One thought is to create an ST_Intersects that uses the geometry > _ST_Intersects instead of geography _ST_Distance > > you might get better speed or you might not. > > CREATE OR REPLACE FUNCTION st_intersectswitht(geography, geography) > RETURNS boolean AS > $$SELECT $1 && $2 AND _ST_Intersects(ST_Transform(geometry($1), > _ST_BestSRID($1)), ST_Transform(geometry($2), _ST_BestSRID($1)))$$ > LANGUAGE 'sql' IMMUTABLE; > > > ________________________________ > From: postgis-users-boun...@postgis.refractions.net > [mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of Nicholas > Bower > Sent: Sunday, May 30, 2010 7:38 PM > To: PostGIS Users Discussion > Subject: Re: [postgis-users] No index usage on geography query plan? > > Well the index says it is being used, however I'm still quite suspicious > because of performance results below. > I attach 3 versions of a simply query (Geography ST_Intersects, Geometry > ST_Intersects, Geography &&) which is a simple square ROI intersection over > 150k rows, each having a single polygon around 50-80 verticies. > 1) Geography ST_Intersects gives 13s :-( > wastac=# explain analyze SELECT count(1) AS count_1 > wastac-# FROM wastac.t_swath_metadata > wastac-# WHERE wastac.t_swath_metadata.quicklook = True > wastac-# AND > ST_Intersects(swath_bounding,ST_GeographyFromText('SRID=4326;POLYGON((104.765625 > -39.0277188402,131.30859375 -39.0277188402,131.30859375 > -15.7076627696,104.765625 -15.7076627696,104.765625 -39.0277188402))')); > Aggregate (cost=13556.17..13556.18 rows=1 width=0) (actual > time=12886.056..12886.057 rows=1 loops=1) > -> Bitmap Heap Scan on t_swath_metadata (cost=506.07..13554.65 rows=607 > width=0) (actual time=17.168..12883.162 rows=8462 loops=1) > Recheck Cond: (swath_bounding && > '0103000020E610000001000000050000000000000000315A402F127C4A8C8343C000000000E06960402F127C4A8C8343C00 > 0000000E0696040BD0E48C6526A2FC00000000000315A40BD0E48C6526A2FC00000000000315A402F127C4A8C8343C0'::geography) > Filter: (quicklook AND (_st_distance(swath_bounding, > '0103000020E610000001000000050000000000000000315A402F127C4A8C8343C000000000E0696 > 0402F127C4A8C8343C000000000E0696040BD0E48C6526A2FC00000000000315A40BD0E48C6526A2FC00000000000315A402F127C4A8C8343C0'::geography, > 0::double pre > cision, false) < 1e-05::double precision)) > -> Bitmap Index Scan on t_swath_metadata_swath_bounding_key > (cost=0.00..505.91 rows=10347 width=0) (actual time=8.148..8.148 rows=1 > 4261 loops=1) > Index Cond: (swath_bounding && > '0103000020E610000001000000050000000000000000315A402F127C4A8C8343C000000000E06960402F127C4A8C834 > 3C000000000E0696040BD0E48C6526A2FC00000000000315A40BD0E48C6526A2FC00000000000315A402F127C4A8C8343C0'::geography) > Total runtime: 12886.287 ms > (7 rows) > > 2) Geometry ST_Intersects gives <1s :-) > wastac=# explain analyze SELECT count(1) AS count_1 > wastac-# FROM wastac.t_swath_metadata_old > wastac-# WHERE quicklook = True > wastac-# AND > ST_Intersects(swath_bounding,ST_GeomFromText('POLYGON((104.765625 > -39.0277188402,131.30859375 -39.0277188402,131.30859375 > -15.7076627696,104.765625 -15.7076627696,104.765625 -39.0277188402))', -1)); > Aggregate (cost=9505.13..9505.14 rows=1 width=0) (actual > time=95.681..95.682 rows=1 loops=1) -> Bitmap Heap Scan on > t_swath_metadata_old (cost=506.77..9503.27 rows=745 width=0) (actual > time=4.198..93.366 rows=7274 loops=1) > Recheck Cond: (swath_bounding && > '010300000001000000050000000000000000315A402F127C4A8C8343C000000000E06960402F127C4A8C8343C000000000E > 0696040BD0E48C6526A2FC00000000000315A40BD0E48C6526A2FC00000000000315A402F127C4A8C8343C0'::geometry) > Filter: (quicklook AND _st_intersects(swath_bounding, > '010300000001000000050000000000000000315A402F127C4A8C8343C000000000E06960402F12 > 7C4A8C8343C000000000E0696040BD0E48C6526A2FC00000000000315A40BD0E48C6526A2FC00000000000315A402F127C4A8C8343C0'::geometry)) > -> Bitmap Index Scan on t_swath_metadata_old_swath_bounding_key > (cost=0.00..506.58 rows=16840 width=0) (actual time=3.557..3.557 ro > ws=9020 loops=1) > Index Cond: (swath_bounding && > '010300000001000000050000000000000000315A402F127C4A8C8343C000000000E06960402F127C4A8C8343C000000 > 000E0696040BD0E48C6526A2FC00000000000315A40BD0E48C6526A2FC00000000000315A402F127C4A8C8343C0'::geometry) > Total runtime: 95.757 ms > (7 rows) > > 3) Geography bounding box < <1s: > wastac=# explain analyze SELECT count(1) AS count_1 > wastac-# FROM wastac.t_swath_metadata > wastac-# WHERE wastac.t_swath_metadata.quicklook = True > wastac-# AND swath_bounding && > ST_GeographyFromText('SRID=4326;POLYGON((104.765625 > -39.0277188402,131.30859375 -39.0277188402,131.30859375 > -15.7076627696,104.765625 -15.7076627696,104.765625 -39.0277188402))'); > Aggregate (cost=10948.03..10948.04 rows=1 width=0) (actual > time=30.583..30.584 rows=1 loops=1) -> Bitmap Heap Scan on > t_swath_metadata (cost=506.38..10943.48 rows=1820 width=0) (actual > time=8.884..27.786 rows=9806 loops=1) > Recheck Cond: (swath_bounding && > '0103000020E610000001000000050000000000000000315A402F127C4A8C8343C000000000E06960402F127C4A8C8343C00 > 0000000E0696040BD0E48C6526A2FC00000000000315A40BD0E48C6526A2FC00000000000315A402F127C4A8C8343C0'::geography) > Filter: quicklook > -> Bitmap Index Scan on t_swath_metadata_swath_bounding_key > (cost=0.00..505.92 rows=10348 width=0) (actual time=7.403..7.403 rows=1 > 4263 loops=1) > Index Cond: (swath_bounding && > '0103000020E610000001000000050000000000000000315A402F127C4A8C8343C000000000E06960402F127C4A8C834 > 3C000000000E0696040BD0E48C6526A2FC00000000000315A40BD0E48C6526A2FC00000000000315A402F127C4A8C8343C0'::geography) > Total runtime: 30.637 ms > (7 rows) > > > On 28 May 2010 16:31, Paragon Corporation <l...@pcorp.us> wrote: >> >> 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. >> > > _______________________________________________ > 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