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 class="Apple-style-span" style="font-family: 'Times New Roman'; font-size: medium; "><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 class="Apple-style-span" style="font-family: 'Times New Roman'; white-space: normal; "><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 class="Apple-style-span" style="font-family: 'Times New Roman'; white-space: normal; "><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 class="Apple-style-span" style="font-family: 'Times New Roman'; white-space: normal; "><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 class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex;">
<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>