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

[Nicholas Bower]

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 <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/a01c2165/attachment.html>