<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META content="text/html; charset=us-ascii" http-equiv=Content-Type>
<META name=GENERATOR content="MSHTML 8.00.7600.16535"></HEAD>
<BODY>
<DIV dir=ltr align=left><SPAN class=397075517-31052010><FONT color=#0000ff
size=2 face=Arial>Nicholas,</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=397075517-31052010><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=397075517-31052010><FONT color=#0000ff
size=2 face=Arial>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.</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=397075517-31052010><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=397075517-31052010><FONT color=#0000ff
size=2 face=Arial>With the index scan you are left with 10,347 records to check
via the slower distance function.</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=397075517-31052010><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=397075517-31052010><FONT size=2
face=Arial>One thought is to create an ST_Intersects that uses the geometry
_ST_Intersects instead of geography _ST_Distance</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=397075517-31052010><FONT size=2
face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=397075517-31052010><FONT size=2
face=Arial>you might get better speed or you might not.</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=397075517-31052010><FONT size=2
face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=397075517-31052010><FONT size=2
face=Arial>CREATE OR REPLACE FUNCTION st_intersectswitht(geography,
geography)<BR> RETURNS boolean AS<BR>$$SELECT $1 && $2 AND
_ST_Intersects(ST_Transform(geometry($1), _ST_BestSRID($1)),
ST_Transform(geometry($2), _ST_BestSRID($1)))$$<BR> LANGUAGE 'sql'
IMMUTABLE;</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=397075517-31052010><FONT size=2
face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=397075517-31052010><FONT size=2
face=Arial></FONT></SPAN> </DIV><FONT color=#0000ff size=2
face=Arial></FONT><BR>
<DIV dir=ltr lang=en-us class=OutlookMessageHeader align=left>
<HR tabIndex=-1>
<FONT size=2 face=Tahoma><B>From:</B>
postgis-users-bounces@postgis.refractions.net
[mailto:postgis-users-bounces@postgis.refractions.net] <B>On Behalf Of
</B>Nicholas Bower<BR><B>Sent:</B> Sunday, May 30, 2010 7:38 PM<BR><B>To:</B>
PostGIS Users Discussion<BR><B>Subject:</B> Re: [postgis-users] No index usage
on geography query plan?<BR></FONT><BR></DIV>
<DIV></DIV>
<DIV>Well the index says it is being used, however I'm still quite suspicious
because of performance results below.</DIV>
<DIV><BR></DIV>
<DIV>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.</DIV>
<DIV><BR></DIV>
<DIV>1) Geography ST_Intersects gives 13s :-(</DIV>
<DIV><BR></DIV>
<DIV>
<DIV>wastac=# explain analyze SELECT count(1) AS count_1</DIV>
<DIV>wastac-# FROM wastac.t_swath_metadata</DIV>
<DIV>wastac-# WHERE wastac.t_swath_metadata.quicklook = True</DIV>
<DIV>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))'));</DIV>
<DIV><BR></DIV>
<DIV> Aggregate (cost=13556.17..13556.18 rows=1 width=0) (actual
time=12886.056..12886.057 rows=1 loops=1)</DIV>
<DIV> -> 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)</DIV>
<DIV> Recheck Cond: (swath_bounding &&
'0103000020E610000001000000050000000000000000315A402F127C4A8C8343C000000000E06960402F127C4A8C8343C00</DIV>
<DIV>0000000E0696040BD0E48C6526A2FC00000000000315A40BD0E48C6526A2FC00000000000315A402F127C4A8C8343C0'::geography)</DIV>
<DIV> Filter: (quicklook AND
(_st_distance(swath_bounding,
'0103000020E610000001000000050000000000000000315A402F127C4A8C8343C000000000E0696</DIV>
<DIV>0402F127C4A8C8343C000000000E0696040BD0E48C6526A2FC00000000000315A40BD0E48C6526A2FC00000000000315A402F127C4A8C8343C0'::geography,
0::double pre</DIV>
<DIV>cision, false) < 1e-05::double precision))</DIV>
<DIV> -> 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</DIV>
<DIV>4261 loops=1)</DIV>
<DIV> Index Cond:
(swath_bounding &&
'0103000020E610000001000000050000000000000000315A402F127C4A8C8343C000000000E06960402F127C4A8C834</DIV>
<DIV>3C000000000E0696040BD0E48C6526A2FC00000000000315A40BD0E48C6526A2FC00000000000315A402F127C4A8C8343C0'::geography)</DIV>
<DIV> Total runtime: 12886.287 ms</DIV>
<DIV>(7 rows)</DIV>
<DIV><BR></DIV>
<DIV><BR></DIV>
<DIV>2) Geometry ST_Intersects gives <1s :-)</DIV>
<DIV><BR></DIV>
<DIV>
<DIV>wastac=# explain analyze SELECT count(1) AS count_1</DIV>
<DIV>wastac-# FROM wastac.t_swath_metadata_old</DIV>
<DIV>wastac-# WHERE quicklook = True</DIV>
<DIV>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));</DIV>
<DIV><BR></DIV>
<DIV> 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)</DIV>
<DIV> Recheck Cond: (swath_bounding &&
'010300000001000000050000000000000000315A402F127C4A8C8343C000000000E06960402F127C4A8C8343C000000000E</DIV>
<DIV>0696040BD0E48C6526A2FC00000000000315A40BD0E48C6526A2FC00000000000315A402F127C4A8C8343C0'::geometry)</DIV>
<DIV> Filter: (quicklook AND
_st_intersects(swath_bounding,
'010300000001000000050000000000000000315A402F127C4A8C8343C000000000E06960402F12</DIV>
<DIV>7C4A8C8343C000000000E0696040BD0E48C6526A2FC00000000000315A40BD0E48C6526A2FC00000000000315A402F127C4A8C8343C0'::geometry))</DIV>
<DIV> -> 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</DIV>
<DIV>ws=9020 loops=1)</DIV>
<DIV> Index Cond:
(swath_bounding &&
'010300000001000000050000000000000000315A402F127C4A8C8343C000000000E06960402F127C4A8C8343C000000</DIV>
<DIV>000E0696040BD0E48C6526A2FC00000000000315A40BD0E48C6526A2FC00000000000315A402F127C4A8C8343C0'::geometry)</DIV>
<DIV> Total runtime: 95.757 ms</DIV>
<DIV>(7 rows)</DIV>
<DIV><BR></DIV></DIV>
<DIV><BR></DIV>
<DIV>3) Geography bounding box < <1s:</DIV>
<DIV><BR></DIV>
<DIV>wastac=# explain analyze SELECT count(1) AS count_1</DIV>
<DIV>wastac-# FROM wastac.t_swath_metadata</DIV>
<DIV>wastac-# WHERE wastac.t_swath_metadata.quicklook = True</DIV>
<DIV>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))');</DIV>
<DIV><BR></DIV>
<DIV> 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)</DIV>
<DIV> Recheck Cond: (swath_bounding &&
'0103000020E610000001000000050000000000000000315A402F127C4A8C8343C000000000E06960402F127C4A8C8343C00</DIV>
<DIV>0000000E0696040BD0E48C6526A2FC00000000000315A40BD0E48C6526A2FC00000000000315A402F127C4A8C8343C0'::geography)</DIV>
<DIV> Filter: quicklook</DIV>
<DIV> -> 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</DIV>
<DIV>4263 loops=1)</DIV>
<DIV> Index Cond:
(swath_bounding &&
'0103000020E610000001000000050000000000000000315A402F127C4A8C8343C000000000E06960402F127C4A8C834</DIV>
<DIV>3C000000000E0696040BD0E48C6526A2FC00000000000315A40BD0E48C6526A2FC00000000000315A402F127C4A8C8343C0'::geography)</DIV>
<DIV> Total runtime: 30.637 ms</DIV>
<DIV>(7 rows)</DIV></DIV>
<DIV><BR></DIV><BR><BR>
<DIV class=gmail_quote>On 28 May 2010 16:31, Paragon Corporation <SPAN
dir=ltr><<A href="mailto:lr@pcorp.us">lr@pcorp.us</A>></SPAN> wrote:<BR>
<BLOCKQUOTE
style="BORDER-LEFT: #ccc 1px solid; MARGIN: 0px 0px 0px 0.8ex; PADDING-LEFT: 1ex"
class=gmail_quote>
<DIV>
<DIV dir=ltr align=left><SPAN><FONT color=#0000ff size=2 face=Arial>Okay I
think the fix is a really simple one</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN><FONT color=#0000ff size=2
face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN><FONT color=#0000ff size=2 face=Arial>Change
your ST_Intersects function to this and see if it behaves right
</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN><FONT color=#0000ff size=2
face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN><FONT color=#0000ff size=2 face=Arial>CREATE OR
REPLACE FUNCTION st_intersects(geography, geography)<BR> RETURNS boolean
AS<BR>'SELECT $1 && $2 AND _ST_Distance($1, $2, 0.0, false) <
0.00001'<BR> LANGUAGE 'sql' IMMUTABLE<BR> COST
100;</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN><FONT color=#0000ff size=2
face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN><FONT color=#0000ff size=2
face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN><FONT color=#0000ff size=2 face=Arial>It should
no longer need the && help to use the index.</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN><FONT color=#0000ff size=2
face=Arial></FONT></SPAN> </DIV></DIV></BLOCKQUOTE></DIV><BR></BODY></HTML>