<!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=710012806-28052010><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 class=710012806-28052010><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=710012806-28052010><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 class=710012806-28052010><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=710012806-28052010><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 class=710012806-28052010><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=710012806-28052010><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=710012806-28052010><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 class=710012806-28052010><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV><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> Thursday, May 27, 2010 6:24 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>Right you are.
<DIV><BR></DIV>
<DIV>
<DIV>wastac=> explain analyze select count(*) from wastac.t_tile_geometry
where 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>wastac-> AND 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><BR></DIV>
<DIV> Aggregate (cost=30.48..30.49 rows=1 width=0) (actual
time=11.459..11.459 rows=1 loops=1) -> Index Scan using
t_tile_geometry_border_key on t_tile_geometry (cost=0.00..30.47 rows=2
width=0) (actual time=0.697..11.416 rows=95 loops=1)</DIV>
<DIV> Index Cond: (border &&
'0103000020E61000000100000005000000F8C610001C305D40B24CBF44BC613FC0D6E253008C385D40DE72F563935640C0DF1B430070AF5C40EE77280AF4654</DIV>
<DIV>0C01EFCC401F4B25C409F3BC1FEEB503FC0F8C610001C305D40B24CBF44BC613FC0'::geography)</DIV>
<DIV> Filter: st_intersects(border,
'0103000020E61000000100000005000000F8C610001C305D40B24CBF44BC613FC0D6E253008C385D40DE72F563935640C0DF1B430070AF5C40EE7728</DIV>
<DIV>0AF46540C01EFCC401F4B25C409F3BC1FEEB503FC0F8C610001C305D40B24CBF44BC613FC0'::geography)</DIV>
<DIV> Total runtime: 11.537 ms</DIV>
<DIV>(5 rows)</DIV>
<DIV><BR></DIV>
<DIV>In case it's relevant this was how I created the geography column from a
geometry one - I noticed the Polygon "restriction" had to be added in the last
statement (ie you don't get it for free from the cast in the first
statement).</DIV>
<DIV><BR></DIV>
<DIV><SPAN style="FONT-FAMILY: 'Times New Roman'; FONT-SIZE: medium"
class=Apple-style-span><PRE style="WORD-WRAP: break-word; WHITE-SPACE: pre-wrap">create table wastac.t_tile_geometry as select *, geography(ST_SetSRID(border, 4326)) as border_new, geography(ST_SetSRID(centre, 4326)) as centre_new from wastac.t_tile_geometry;</PRE><PRE style="WORD-WRAP: break-word; WHITE-SPACE: pre-wrap"><SPAN style="FONT-FAMILY: 'Times New Roman'; WHITE-SPACE: normal" class=Apple-style-span><PRE style="WORD-WRAP: break-word; WHITE-SPACE: pre-wrap">alter table wastac.t_tile_geometry_new drop column border;</PRE><PRE style="WORD-WRAP: break-word; WHITE-SPACE: pre-wrap"><SPAN style="FONT-FAMILY: 'Times New Roman'; WHITE-SPACE: normal" class=Apple-style-span><PRE style="WORD-WRAP: break-word; WHITE-SPACE: pre-wrap">alter table wastac.t_tile_geometry_new rename column border_new to border;</PRE><PRE style="WORD-WRAP: break-word; WHITE-SPACE: pre-wrap"><SPAN style="FONT-FAMILY: 'Times New Roman'; WHITE-SPACE: normal" class=Apple-style-span><PRE style="WORD-WRAP: break-word; WHITE-SPACE: pre-wrap">alter table wastac.t_tile_geometry alter column border set data type geography(Polygon,4326);</PRE><PRE style="WORD-WRAP: break-word; WHITE-SPACE: pre-wrap"><BR></PRE></SPAN></PRE></SPAN></PRE></SPAN></PRE></SPAN></DIV>
<DIV></DIV></DIV>
<DIV><BR></DIV>
<DIV><BR><BR>
<DIV class=gmail_quote>On 27 May 2010 15:42, 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>Nick,</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN><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><FONT color=#0000ff size=2
face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN><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><FONT color=#0000ff size=2
face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN><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><FONT color=#0000ff size=2
face=Arial></FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN><FONT color=#0000ff size=2 face=Arial>&&
+ _ST_Intersects</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>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><FONT color=#0000ff size=2
face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN><FONT color=#0000ff size=2 face=Arial>So I bet
this uses an index and is much faster</FONT></SPAN><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
color=#000000 size=3 face="Times New Roman">
<DIV class=im>select count(*) from wastac.t_tile_geometry where<BR></DIV>
<DIV class=im>border &&
ST_GeographyFromText('SRID=4326;POLYGON((116.751709<BR></DIV></FONT>
<DIV><FONT color=#000000 size=3 face="Times New Roman">
<DIV class=im>-31.381779,116.883545 -32.676373,114.741211
-32.796510,114.796143<BR></DIV>-31.316101,116.751709 -31.381779))')<SPAN> 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><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></SPAN>
<DIV><SPAN></SPAN><FONT face=Arial><FONT color=#0000ff><FONT
size=2>T<SPAN>hanks</SPAN></FONT></FONT></FONT></DIV>
<DIV><SPAN></SPAN><SPAN></SPAN><FONT face=Arial><FONT color=#0000ff><FONT
size=2>R<SPAN>egina and Leo</SPAN></FONT></FONT></FONT></DIV>
<DIV><SPAN></SPAN><SPAN></SPAN><FONT face=Arial><FONT color=#0000ff><FONT
size=2><A href="http://www.postgis.us"
target=_blank>h<SPAN>ttp://www.postgis.us</SPAN></A></FONT></FONT></FONT><BR></DIV></DIV>
<DIV dir=ltr lang=en-us align=left>
<HR>
<FONT size=2 face=Tahoma><B>From:</B> <A
href="mailto:postgis-users-bounces@postgis.refractions.net"
target=_blank>postgis-users-bounces@postgis.refractions.net</A> [mailto:<A
href="mailto:postgis-users-bounces@postgis.refractions.net"
target=_blank>postgis-users-bounces@postgis.refractions.net</A>] <B>On Behalf
Of </B>Nicholas Bower<BR><B>Sent:</B> Wednesday, May 26, 2010 6:42 PM
<DIV class=im><BR><B>To:</B> PostGIS Users Discussion<BR><B>Subject:</B> Re:
[postgis-users] No index usage on geography query
plan?<BR></DIV></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
<DIV>
<DIV></DIV>
<DIV class=h5><BR>agree should still be using the spatial index, so probably
making the index<BR>cost higher than it should<BR></DIV></DIV></BLOCKQUOTE>
<DIV>
<DIV></DIV>
<DIV class=h5>
<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>-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></DIV></DIV></DIV><BR>_______________________________________________<BR>postgis-users
mailing list<BR><A
href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</A><BR><A
href="http://postgis.refractions.net/mailman/listinfo/postgis-users"
target=_blank>http://postgis.refractions.net/mailman/listinfo/postgis-users</A><BR><BR></BLOCKQUOTE></DIV><BR></DIV></BODY></HTML>