<div><div>I understand your explanation now - it's why our alternative queries over a pre-calculated 20km grid from overlaying the large boundary approached geometry in spatial performance (but then we incur far worse overall performance because of the sheer number of features in the query).</div>
</div><div><br></div>The reason we are querying geographies instead of geometries is because we have large-scale global coverages that cross IDL and get warped near poles.<div><br></div><div>To me, querying large scale features is the whole reason for geography, and so ideally my scenario should reflect best case performance not worst for geography.</div>
<div><br></div><div>Otherwise if I had small features (minimal warping - eg my 20km grid), I'd just continue as I have for years and use geometry operators and handle IDL wrapping by adding 360 onto any negative longitude. Not fancy but fast.</div>
<div><br></div><br><div class="gmail_quote">On 1 June 2010 12:27, 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 bgcolor="#ffffff">
<div dir="ltr" align="left"><span><font color="#0000ff" size="2" face="Arial">Paul,</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">On that thought. Remember how geometry intersects
performance significantly increased with prepared geometry algorithm, are we
using that same kind of prepared geometry logic for
geography.</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">Just thinking out loud that aside from the slower algorithm,
for cases like these where thousands of records need to be checked by the
non-index check, we are losing performance there too. I imagine that may
be an even easier enhancement.</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">Not sure it makes a difference in this particular case since
Nicholas bounding constant geography is pretty simple.</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">Nicholas -- as Paul stated -- its not the index that is orders
of magnitude slower, its the secondary check. For most use cases (needle
in a hay stack where you are trying to get rid of 1,000,000 records and check
100 or so candidates, the speed is pretty decent and not too far off from
geometry).</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">Decent once we fix the ST_Intersects minor bug that prevents
the index from being used. Though we should have a caveat somewhere
explaining in detail these scenarios.</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">Hope that helps,</font></span></div>
<div dir="ltr" align="left"><span><font color="#0000ff" size="2" face="Arial">Regina</font></span></div><font color="#0000ff" size="2" face="Arial"></font><br>
<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>Paul
Ramsey<br><b>Sent:</b> Monday, May 31, 2010 9:20 PM<div><div></div><div class="h5"><br><b>To:</b> PostGIS Users
Discussion<br><b>Subject:</b> Re: [postgis-users] No index usage on geography
query plan?<br></div></div></font><br></div><div><div></div><div class="h5">
<div></div>
<div>FYI, it is not the index that is slower, it is the op. The index is
actually (surprisingly) faster.</div>
<div><br></div>
<div>P<br><br><br></div>
<div><br>On May 31, 2010, at 5:06 PM, Nicholas Bower <<a href="mailto:nick@petangent.net" target="_blank">nick@petangent.net</a>> wrote:<br><br></div>
<div></div>
<blockquote type="cite">
<div>In the meantime perhaps someone could add a warning to the docs that
geography indexes are an order of magnitude slower than geometries for
intersections? It's pretty important stuff for anyone appraising a
migration.
<div><br></div>
<div>Actually I was after containment (db polygons enclose search ROI) but
that's a feature yet to come I know.<br><br></div>
<div>No complaints about on the basis it's free, but people have to admit 10s
for intersecting 150k polygons is not stellar given how we've been spoiled
with geometries in the past.</div>
<div><br></div>
<div>Anyway, maybe a warning is in order for people considering moving across
what do you think?</div>
<div><br></div>
<div><br>
<div class="gmail_quote">On 1 June 2010 07:00, Paul Ramsey <span dir="ltr"><<a href="mailto:pramsey@opengeo.org" target="_blank"></a><a href="mailto:pramsey@opengeo.org" target="_blank">pramsey@opengeo.org</a>></span>
wrote:<br>
<blockquote style="border-left:#ccc 1px solid;margin:0px 0px 0px 0.8ex;padding-left:1ex" class="gmail_quote">Actually it's slower because the calculations require lots
of<br>transcendental math. Anyhow, it's slower. If anyone wants a quote
on<br>speed improvements, I'm happy to provide one, I have some good
ideas<br>about how to speed things up with some better data structures
and<br>caching.<br><font color="#888888"><br>P.<br></font>
<div>
<div></div>
<div><br>On Mon, May 31, 2010 at 2:48 PM, Paragon Corporation
<<a href="mailto:lr@pcorp.us" target="_blank"></a><a href="mailto:lr@pcorp.us" target="_blank">lr@pcorp.us</a>> wrote:<br>>
Nicholas,<br>><br>> I fear that may be the way it is. The
intersects functionality of geography<br>> is slower than intersects of
geometry because it piggy backs on the distance<br>> function rather than
using an intersection matrix.<br>><br>> With the index scan you are
left with 10,347 records to check via the slower<br>> distance
function.<br>><br>> One thought is to create an ST_Intersects that
uses the geometry<br>> _ST_Intersects instead of geography
_ST_Distance<br>><br>> you might get better speed or you might
not.<br>><br>> CREATE OR REPLACE FUNCTION
st_intersectswitht(geography, geography)<br>> RETURNS boolean
AS<br>> $$SELECT $1 && $2 AND
_ST_Intersects(ST_Transform(geometry($1),<br>> _ST_BestSRID($1)),
ST_Transform(geometry($2), _ST_BestSRID($1)))$$<br>> LANGUAGE
'sql' IMMUTABLE;<br>><br>><br>>
________________________________<br>> From: <a href="mailto:postgis-users-bounces@postgis.refractions.net" target="_blank"></a><a href="mailto:postgis-users-bounces@postgis.refractions.net" target="_blank">postgis-users-bounces@postgis.refractions.net</a><br>
>
[mailto:<a href="mailto:postgis-users-bounces@postgis.refractions.net" target="_blank"></a><a href="mailto:postgis-users-bounces@postgis.refractions.net" target="_blank">postgis-users-bounces@postgis.refractions.net</a>]
On Behalf Of Nicholas<br>> Bower<br>> Sent: Sunday, May 30, 2010 7:38
PM<br>> To: PostGIS Users Discussion<br>> Subject: Re: [postgis-users]
No index usage on geography query plan?<br>><br>> Well the index says
it is being used, however I'm still quite suspicious<br>> because of
performance results below.<br>> I attach 3 versions of a simply query
(Geography ST_Intersects, Geometry<br>> ST_Intersects, Geography
&&) which is a simple square ROI intersection over<br>> 150k
rows, each having a single polygon around 50-80 verticies.<br>> 1)
Geography ST_Intersects gives 13s :-(<br>> wastac=# explain analyze
SELECT count(1) AS count_1<br>> wastac-# FROM
wastac.t_swath_metadata<br>> wastac-# WHERE
wastac.t_swath_metadata.quicklook = True<br>> wastac-# AND<br>>
ST_Intersects(swath_bounding,ST_GeographyFromText('SRID=4326;POLYGON((104.765625<br>>
-39.0277188402,131.30859375 -39.0277188402,131.30859375<br>>
-15.7076627696,104.765625 -15.7076627696,104.765625
-39.0277188402))'));<br>> Aggregate (cost=13556.17..13556.18
rows=1 width=0) (actual<br>> time=12886.056..12886.057 rows=1
loops=1)<br>> -> Bitmap Heap Scan on
t_swath_metadata (cost=506.07..13554.65 rows=607<br>> width=0)
(actual time=17.168..12883.162 rows=8462 loops=1)<br>>
Recheck Cond: (swath_bounding &&<br>>
'0103000020E610000001000000050000000000000000315A402F127C4A8C8343C000000000E06960402F127C4A8C8343C00<br>>
0000000E0696040BD0E48C6526A2FC00000000000315A40BD0E48C6526A2FC00000000000315A402F127C4A8C8343C0'::geography)<br>>
Filter: (quicklook AND
(_st_distance(swath_bounding,<br>>
'0103000020E610000001000000050000000000000000315A402F127C4A8C8343C000000000E0696<br>>
0402F127C4A8C8343C000000000E0696040BD0E48C6526A2FC00000000000315A40BD0E48C6526A2FC00000000000315A402F127C4A8C8343C0'::geography,<br>>
0::double pre<br>> cision, false) < 1e-05::double precision))<br>>
-> Bitmap Index Scan on
t_swath_metadata_swath_bounding_key<br>> (cost=0.00..505.91
rows=10347 width=0) (actual time=8.148..8.148 rows=1<br>> 4261
loops=1)<br>>
Index Cond: (swath_bounding &&<br>>
'0103000020E610000001000000050000000000000000315A402F127C4A8C8343C000000000E06960402F127C4A8C834<br>>
3C000000000E0696040BD0E48C6526A2FC00000000000315A40BD0E48C6526A2FC00000000000315A402F127C4A8C8343C0'::geography)<br>>
Total runtime: 12886.287 ms<br>> (7 rows)<br>><br>> 2)
Geometry ST_Intersects gives <1s :-)<br>> wastac=# explain analyze
SELECT count(1) AS count_1<br>> wastac-# FROM
wastac.t_swath_metadata_old<br>> wastac-# WHERE quicklook = True<br>>
wastac-# AND<br>>
ST_Intersects(swath_bounding,ST_GeomFromText('POLYGON((104.765625<br>>
-39.0277188402,131.30859375 -39.0277188402,131.30859375<br>>
-15.7076627696,104.765625 -15.7076627696,104.765625 -39.0277188402))',
-1));<br>> Aggregate (cost=9505.13..9505.14 rows=1 width=0)
(actual<br>> time=95.681..95.682 rows=1 loops=1) ->
Bitmap Heap Scan on<br>> t_swath_metadata_old
(cost=506.77..9503.27 rows=745 width=0) (actual<br>>
time=4.198..93.366 rows=7274 loops=1)<br>>
Recheck Cond: (swath_bounding &&<br>>
'010300000001000000050000000000000000315A402F127C4A8C8343C000000000E06960402F127C4A8C8343C000000000E<br>>
0696040BD0E48C6526A2FC00000000000315A40BD0E48C6526A2FC00000000000315A402F127C4A8C8343C0'::geometry)<br>>
Filter: (quicklook AND
_st_intersects(swath_bounding,<br>>
'010300000001000000050000000000000000315A402F127C4A8C8343C000000000E06960402F12<br>>
7C4A8C8343C000000000E0696040BD0E48C6526A2FC00000000000315A40BD0E48C6526A2FC00000000000315A402F127C4A8C8343C0'::geometry))<br>>
-> Bitmap Index Scan on
t_swath_metadata_old_swath_bounding_key<br>> (cost=0.00..506.58
rows=16840 width=0) (actual time=3.557..3.557 ro<br>> ws=9020
loops=1)<br>>
Index Cond: (swath_bounding &&<br>>
'010300000001000000050000000000000000315A402F127C4A8C8343C000000000E06960402F127C4A8C8343C000000<br>>
000E0696040BD0E48C6526A2FC00000000000315A40BD0E48C6526A2FC00000000000315A402F127C4A8C8343C0'::geometry)<br>>
Total runtime: 95.757 ms<br>> (7 rows)<br>><br>> 3) Geography
bounding box < <1s:<br>> wastac=# explain analyze SELECT
count(1) AS count_1<br>> wastac-# FROM wastac.t_swath_metadata<br>>
wastac-# WHERE wastac.t_swath_metadata.quicklook = True<br>> wastac-#
AND swath_bounding &&<br>>
ST_GeographyFromText('SRID=4326;POLYGON((104.765625<br>>
-39.0277188402,131.30859375 -39.0277188402,131.30859375<br>>
-15.7076627696,104.765625 -15.7076627696,104.765625
-39.0277188402))');<br>> Aggregate (cost=10948.03..10948.04
rows=1 width=0) (actual<br>> time=30.583..30.584 rows=1
loops=1) -> Bitmap Heap Scan on<br>>
t_swath_metadata (cost=506.38..10943.48 rows=1820 width=0)
(actual<br>> time=8.884..27.786 rows=9806 loops=1)<br>>
Recheck Cond: (swath_bounding &&<br>>
'0103000020E610000001000000050000000000000000315A402F127C4A8C8343C000000000E06960402F127C4A8C8343C00<br>>
0000000E0696040BD0E48C6526A2FC00000000000315A40BD0E48C6526A2FC00000000000315A402F127C4A8C8343C0'::geography)<br>>
Filter: quicklook<br>>
-> Bitmap Index Scan on
t_swath_metadata_swath_bounding_key<br>> (cost=0.00..505.92
rows=10348 width=0) (actual time=7.403..7.403 rows=1<br>> 4263
loops=1)<br>>
Index Cond: (swath_bounding &&<br>>
'0103000020E610000001000000050000000000000000315A402F127C4A8C8343C000000000E06960402F127C4A8C834<br>>
3C000000000E0696040BD0E48C6526A2FC00000000000315A40BD0E48C6526A2FC00000000000315A402F127C4A8C8343C0'::geography)<br>>
Total runtime: 30.637 ms<br>> (7 rows)<br>><br>><br>> On
28 May 2010 16:31, Paragon Corporation <<a href="mailto:lr@pcorp.us" target="_blank"></a><a href="mailto:lr@pcorp.us" target="_blank">lr@pcorp.us</a>>
wrote:<br>>><br>>> Okay I think the fix is a really simple
one<br>>><br>>> Change your ST_Intersects function to this and
see if it behaves right<br>>><br>>> 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;<br>>><br>>><br>>> It should no longer need the
&& help to use the index.<br>>><br>><br></div></div>
<div>
<div></div>
<div>> _______________________________________________<br>>
postgis-users mailing list<br>> <a href="mailto:postgis-users@postgis.refractions.net" target="_blank"></a><a href="mailto:postgis-users@postgis.refractions.net" target="_blank">postgis-users@postgis.refractions.net</a><br>
>
<a href="http://postgis.refractions.net/mailman/listinfo/postgis-users" target="_blank"></a><a href="http://postgis.refractions.net/mailman/listinfo/postgis-users" target="_blank">http://postgis.refractions.net/mailman/listinfo/postgis-users</a><br>
><br>><br>_______________________________________________<br>postgis-users
mailing list<br><a href="mailto:postgis-users@postgis.refractions.net" target="_blank"></a><a href="mailto:postgis-users@postgis.refractions.net" target="_blank">postgis-users@postgis.refractions.net</a><br><a href="http://postgis.refractions.net/mailman/listinfo/postgis-users" target="_blank"></a><a href="http://postgis.refractions.net/mailman/listinfo/postgis-users" target="_blank">http://postgis.refractions.net/mailman/listinfo/postgis-users</a><br>
</div></div></blockquote></div><br></div></div></blockquote>
<blockquote type="cite">
<div><span>_______________________________________________</span><br><span>postgis-users
mailing list</span><br><span><a href="mailto:postgis-users@postgis.refractions.net" target="_blank">postgis-users@postgis.refractions.net</a></span><br><span><a href="http://postgis.refractions.net/mailman/listinfo/postgis-users" target="_blank">http://postgis.refractions.net/mailman/listinfo/postgis-users</a></span><br>
</div></blockquote></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>