<!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 bgColor=#ffffff>
<DIV dir=ltr align=left><SPAN class=749301702-01062010><FONT color=#0000ff
size=2 face=Arial>Paul,</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=749301702-01062010><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=749301702-01062010><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 class=749301702-01062010><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=749301702-01062010><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 class=749301702-01062010><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=749301702-01062010><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 class=749301702-01062010><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=749301702-01062010><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 class=749301702-01062010><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=749301702-01062010><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 class=749301702-01062010><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=749301702-01062010><FONT color=#0000ff
size=2 face=Arial>Hope that helps,</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=749301702-01062010><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 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>Paul
Ramsey<BR><B>Sent:</B> Monday, May 31, 2010 9:20 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>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">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"><A
href="mailto:pramsey@opengeo.org">pramsey@opengeo.org</A></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 class=h5><BR>On Mon, May 31, 2010 at 2:48 PM, Paragon Corporation
<<A href="mailto:lr@pcorp.us"><A
href="mailto:lr@pcorp.us">lr@pcorp.us</A></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"><A
href="mailto:postgis-users-bounces@postgis.refractions.net">postgis-users-bounces@postgis.refractions.net</A></A><BR>>
[mailto:<A href="mailto:postgis-users-bounces@postgis.refractions.net"><A
href="mailto:postgis-users-bounces@postgis.refractions.net">postgis-users-bounces@postgis.refractions.net</A></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"><A
href="mailto:lr@pcorp.us">lr@pcorp.us</A></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 class=h5>> _______________________________________________<BR>>
postgis-users mailing list<BR>> <A
href="mailto:postgis-users@postgis.refractions.net"><A
href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</A></A><BR>>
<A href="http://postgis.refractions.net/mailman/listinfo/postgis-users"
target=_blank><A
href="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</A></A><BR>><BR>><BR>_______________________________________________<BR>postgis-users
mailing list<BR><A href="mailto:postgis-users@postgis.refractions.net"><A
href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</A></A><BR><A
href="http://postgis.refractions.net/mailman/listinfo/postgis-users"
target=_blank><A
href="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</A></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">postgis-users@postgis.refractions.net</A></SPAN><BR><SPAN><A
href="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</A></SPAN><BR></DIV></BLOCKQUOTE></BODY></HTML>