<!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.6001.18904"></HEAD>
<BODY>
<DIV dir=ltr align=left><SPAN class=000013605-27052010><FONT color=#0000ff
size=2 face=Arial>Nick,</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=000013605-27052010><FONT color=#0000ff
size=2 face=Arial>Okay we are seeing the same issue with our fastfoods data even
with smaller windows. I think the clue is the plan
here.</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=000013605-27052010><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=000013605-27052010><FONT color=#0000ff
size=2 face=Arial>The ST_Intersects geography function seems to be treated as a
blackbox rather than a transparent function composed of && and
_ST_Distance</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=000013605-27052010><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=000013605-27052010><FONT color=#0000ff
size=2 face=Arial>The magic of the geometry ST_Intersects is that the query plan
can see into the function and decomposes it into</FONT></SPAN><SPAN
class=000013605-27052010><FONT color=#0000ff size=2
face=Arial></FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=000013605-27052010><FONT color=#0000ff
size=2 face=Arial>&& + _ST_Intersects</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=000013605-27052010><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=000013605-27052010><FONT color=#0000ff
size=2 face=Arial>For some reason, that's just not happening with
geography. Maybe Paul or Mark have a clue why that is.</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=000013605-27052010><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=000013605-27052010><FONT color=#0000ff
size=2 face=Arial>So I bet this uses an index and is much
faster</FONT></SPAN><SPAN class=000013605-27052010><FONT color=#0000ff size=2
face=Arial></FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=000013605-27052010><FONT color=#0000ff
size=2 face=Arial><FONT color=#000000 size=3 face="Times New Roman">select
count(*) from wastac.t_tile_geometry where<BR>border &&
ST_GeographyFromText('SRID=4326;POLYGON((116.751709<BR></FONT>
<DIV class=im><FONT color=#000000 size=3
face="Times New Roman">-31.381779,116.883545 -32.676373,114.741211
-32.796510,114.796143<BR>-31.316101,116.751709 -31.381779))')<SPAN
class=000013605-27052010> AND </SPAN>ST_Intersects(border,
ST_GeographyFromText('SRID=4326;POLYGON((116.751709 -31.381779,116.883545
-32.676373,114.741211 -32.796510,114.796143-31.316101,116.751709
-31.381779))'));</FONT></DIV>
<DIV class=im><FONT color=#000000 size=3
face="Times New Roman"></FONT> </DIV><FONT color=#000000 size=3
face="Times New Roman"></FONT></FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=000013605-27052010></SPAN>
<DIV class=im><SPAN class=000013605-27052010></SPAN><FONT face=Arial><FONT
color=#0000ff><FONT size=2>T<SPAN
class=000013605-27052010>hanks</SPAN></FONT></FONT></FONT></DIV>
<DIV class=im><SPAN class=000013605-27052010></SPAN><SPAN
class=000013605-27052010></SPAN><FONT face=Arial><FONT color=#0000ff><FONT
size=2>R<SPAN class=000013605-27052010>egina and
Leo</SPAN></FONT></FONT></FONT></DIV>
<DIV class=im><SPAN class=000013605-27052010></SPAN><SPAN
class=000013605-27052010></SPAN><FONT face=Arial><FONT color=#0000ff><FONT
size=2><A href="http://www.postgis.us">h<SPAN
class=000013605-27052010>ttp://www.postgis.us</A></SPAN></FONT></FONT></FONT><BR></DIV></DIV>
<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> Wednesday, May 26, 2010 6:42 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 class=gmail_quote>
<DIV> </DIV>
<BLOCKQUOTE
style="BORDER-LEFT: #ccc 1px solid; MARGIN: 0px 0px 0px 0.8ex; PADDING-LEFT: 1ex"
class=gmail_quote>That does look like a pretty huge bounding polygon, but the
geography we<BR>agree should still be using the spatial index, so probably
making the index<BR>cost higher than it should<BR></BLOCKQUOTE>
<DIV><BR></DIV>
<DIV>Fyi the border values are are simply composed of a regular 20km grid of
ajoining polygon squares covering Australia (about 1.3M tiles).
Incidentally, with the border of tiles being square, the bounding box *is*
the shape I guess. It's not relevant, but the query returns a count 95 of
these 20km tiles.</DIV>
<DIV><BR></DIV>
<DIV>We're dealing with satellite data - the datasets themselves have far bigger
coverages (different table entirely), but this is why you've noticed the "region
of interest" in the query is large.</DIV>
<DIV><BR></DIV>
<BLOCKQUOTE
style="BORDER-LEFT: #ccc 1px solid; MARGIN: 0px 0px 0px 0.8ex; PADDING-LEFT: 1ex"
class=gmail_quote><BR>Nick,<BR><BR>Can you just for contrast, try to force it
to use the index by doing<BR><BR>set enable_seqscan =
off;<BR><BR> explain analyze select count(*) from wastac.t_tile_geometry
where<BR>ST_Intersects(border,
ST_GeographyFromText('SRID=4326;POLYGON((116.751709<BR>
<DIV class=im>-31.381779,116.883545 -32.676373,114.741211
-32.796510,114.796143<BR>-31.316101,116.751709
-31.381779))'));<BR></DIV></BLOCKQUOTE>
<DIV><BR></DIV>
<DIV>Strange - no difference.</DIV>
<DIV><BR></DIV>
<DIV>
<DIV>wastac=> set enable_seqscan = off;</DIV>
<DIV>SET</DIV>
<DIV>wastac=> explain analyze select count(*) from wastac.t_tile_geometry
where ST_Intersects(border, ST_GeographyFromText('SRID=4326;POLYGON((116.751709
-31.381779,116.883545 -32.676373,114.741211
-32.796510,114.796143-31.316101,116.751709 -31.381779))'));</DIV>
<DIV>
QUERY PLAN
</DIV>
<DIV>--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------</DIV>
<DIV> Aggregate (cost=10000364056.80..10000364056.81 rows=1 width=0)
(actual time=8909.585..8909.586 rows=1 loops=1)</DIV>
<DIV> -> Seq Scan on t_tile_geometry
(cost=10000000000.00..10000362993.14 rows=425463 width=0) (actual
time=2028.827..8909.533 rows=95 loops=1)</DIV>
<DIV> Filter: st_intersects(border,
'0103000020E61000000100000005000000F8C610001C305D40B24CBF44BC613FC0D6E253008C385D40DE72F563935640C0DF1B430070AF5C40EE77280AF46540C01EFCC401F4B25C409F3BC1FEEB503FC0F8C610001C305D40B24CBF44BC613FC0'::geography)</DIV>
<DIV> Total runtime: 8909.626 ms</DIV>
<DIV>(4 rows)</DIV>
<DIV><BR></DIV></DIV>
<DIV> </DIV></DIV></BODY></HTML>