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

Mon May 31 11:48:43 PDT 2010

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-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at 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.76
5625 -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 &&
'0103000020E610000001000000050000000000000000315A402F127C4A8C8343C000000000E
06960402F127C4A8C8343C00
0000000E0696040BD0E48C6526A2FC00000000000315A40BD0E48C6526A2FC00000000000315
A402F127C4A8C8343C0'::geography)
         Filter: (quicklook AND (_st_distance(swath_bounding,
'0103000020E610000001000000050000000000000000315A402F127C4A8C8343C000000000E
0696
0402F127C4A8C8343C000000000E0696040BD0E48C6526A2FC00000000000315A40BD0E48C65
26A2FC00000000000315A402F127C4A8C8343C0'::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 &&
'0103000020E610000001000000050000000000000000315A402F127C4A8C8343C000000000E
06960402F127C4A8C834
3C000000000E0696040BD0E48C6526A2FC00000000000315A40BD0E48C6526A2FC0000000000
0315A402F127C4A8C8343C0'::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 &&
'010300000001000000050000000000000000315A402F127C4A8C8343C000000000E06960402
F127C4A8C8343C000000000E
0696040BD0E48C6526A2FC00000000000315A40BD0E48C6526A2FC00000000000315A402F127
C4A8C8343C0'::geometry)
         Filter: (quicklook AND _st_intersects(swath_bounding,
'010300000001000000050000000000000000315A402F127C4A8C8343C000000000E06960402
F12
7C4A8C8343C000000000E0696040BD0E48C6526A2FC00000000000315A40BD0E48C6526A2FC0
0000000000315A402F127C4A8C8343C0'::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 &&
'010300000001000000050000000000000000315A402F127C4A8C8343C000000000E06960402
F127C4A8C8343C000000
000E0696040BD0E48C6526A2FC00000000000315A40BD0E48C6526A2FC00000000000315A402
F127C4A8C8343C0'::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 &&
'0103000020E610000001000000050000000000000000315A402F127C4A8C8343C000000000E
06960402F127C4A8C8343C00
0000000E0696040BD0E48C6526A2FC00000000000315A40BD0E48C6526A2FC00000000000315
A402F127C4A8C8343C0'::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 &&
'0103000020E610000001000000050000000000000000315A402F127C4A8C8343C000000000E
06960402F127C4A8C834
3C000000000E0696040BD0E48C6526A2FC00000000000315A40BD0E48C6526A2FC0000000000
0315A402F127C4A8C8343C0'::geography)
 Total runtime: 30.637 ms
(7 rows)

On 28 May 2010 16:31, Paragon Corporation <lr at 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.

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20100531/ab753b03/attachment.html>