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