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">pramsey@opengeo.org</a>></span> wrote:<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex;">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">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">postgis-users-bounces@postgis.refractions.net</a><br>
> [mailto:<a href="mailto:postgis-users-bounces@postgis.refractions.net">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">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 class="h5">> _______________________________________________<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>
><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>
</div></div></blockquote></div><br></div>