[postgis-users] No index usage on geography query plan?

[Paragon Corporation]

Paul,
 
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.
 
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.
 
Not sure it makes a difference in this particular case since Nicholas
bounding constant geography is pretty simple.
 
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).
 
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.
 
Hope that helps,
Regina


  _____  

From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of Paul
Ramsey
Sent: Monday, May 31, 2010 9:20 PM
To: PostGIS Users Discussion
Subject: Re: [postgis-users] No index usage on geography query plan?


FYI, it is not the index that is slower, it is the op. The index is actually
(surprisingly) faster.

P




On May 31, 2010, at 5:06 PM, Nicholas Bower <nick at petangent.net> wrote:



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. 

Actually I was after containment (db polygons enclose search ROI) but that's
a feature yet to come I know.


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.

Anyway, maybe a warning is in order for people considering moving across
what do you think?


On 1 June 2010 07:00, Paul Ramsey < <mailto:pramsey at opengeo.org>
pramsey at opengeo.org> wrote:


Actually it's slower because the calculations require lots of
transcendental math. Anyhow, it's slower. If anyone wants a quote on
speed improvements, I'm happy to provide one, I have some good ideas
about how to speed things up with some better data structures and
caching.

P.


On Mon, May 31, 2010 at 2:48 PM, Paragon Corporation < <mailto:lr at pcorp.us>
lr at pcorp.us> wrote:
> Nicholas,
>
> I fear that may be the way it is.  The intersects functionality of
geography
> is slower than intersects of geometry because it piggy backs on the
distance
> function rather than using an intersection matrix.
>
> With the index scan you are left with 10,347 records to check via the
slower
> distance function.
>
> One thought is to create an ST_Intersects that uses the geometry
> _ST_Intersects instead of geography _ST_Distance
>
> you might get better speed or you might not.
>
> CREATE OR REPLACE FUNCTION st_intersectswitht(geography, geography)
>   RETURNS boolean AS
> $$SELECT $1 && $2 AND _ST_Intersects(ST_Transform(geometry($1),
> _ST_BestSRID($1)), ST_Transform(geometry($2), _ST_BestSRID($1)))$$
>   LANGUAGE 'sql' IMMUTABLE;
>
>
> ________________________________
> From:  <mailto:postgis-users-bounces at postgis.refractions.net>
postgis-users-bounces at postgis.refractions.net
> [mailto: <mailto:postgis-users-bounces at postgis.refractions.net>
postgis-users-bounces at postgis.refractions.net] On Behalf Of Nicholas
> Bower
> Sent: Sunday, May 30, 2010 7:38 PM
> To: PostGIS Users Discussion
> Subject: Re: [postgis-users] No index usage on geography query plan?
>
> Well the index says it is being used, however I'm still quite suspicious
> because of performance results below.
> I attach 3 versions of a simply query (Geography ST_Intersects, Geometry
> ST_Intersects, Geography &&) which is a simple square ROI intersection
over
> 150k rows, each having a single polygon around 50-80 verticies.
> 1) Geography ST_Intersects gives 13s :-(
> wastac=# explain analyze SELECT count(1) AS count_1
> wastac-# FROM wastac.t_swath_metadata
> wastac-# WHERE wastac.t_swath_metadata.quicklook = True
> wastac-#  AND
>
ST_Intersects(swath_bounding,ST_GeographyFromText('SRID=4326;POLYGON((104.76
5625
> -39.0277188402,131.30859375 -39.0277188402,131.30859375
> -15.7076627696,104.765625 -15.7076627696,104.765625 -39.0277188402))'));
>  Aggregate  (cost=13556.17..13556.18 rows=1 width=0) (actual
> time=12886.056..12886.057 rows=1 loops=1)
>    ->  Bitmap Heap Scan on t_swath_metadata  (cost=506.07..13554.65
rows=607
> width=0) (actual time=17.168..12883.162 rows=8462 loops=1)
>          Recheck Cond: (swath_bounding &&
>
'0103000020E610000001000000050000000000000000315A402F127C4A8C8343C000000000E
06960402F127C4A8C8343C00
>
0000000E0696040BD0E48C6526A2FC00000000000315A40BD0E48C6526A2FC00000000000315
A402F127C4A8C8343C0'::geography)
>          Filter: (quicklook AND (_st_distance(swath_bounding,
>
'0103000020E610000001000000050000000000000000315A402F127C4A8C8343C000000000E
0696
>
0402F127C4A8C8343C000000000E0696040BD0E48C6526A2FC00000000000315A40BD0E48C65
26A2FC00000000000315A402F127C4A8C8343C0'::geography,
> 0::double pre
> cision, false) < 1e-05::double precision))
>          ->  Bitmap Index Scan on t_swath_metadata_swath_bounding_key
>  (cost=0.00..505.91 rows=10347 width=0) (actual time=8.148..8.148 rows=1
> 4261 loops=1)
>                Index Cond: (swath_bounding &&
>
'0103000020E610000001000000050000000000000000315A402F127C4A8C8343C000000000E
06960402F127C4A8C834
>
3C000000000E0696040BD0E48C6526A2FC00000000000315A40BD0E48C6526A2FC0000000000
0315A402F127C4A8C8343C0'::geography)
>  Total runtime: 12886.287 ms
> (7 rows)
>
> 2) Geometry ST_Intersects gives <1s :-)
> wastac=# explain analyze SELECT count(1) AS count_1
> wastac-# FROM wastac.t_swath_metadata_old
> wastac-# WHERE quicklook = True
> wastac-# AND
> ST_Intersects(swath_bounding,ST_GeomFromText('POLYGON((104.765625
> -39.0277188402,131.30859375 -39.0277188402,131.30859375
> -15.7076627696,104.765625 -15.7076627696,104.765625 -39.0277188402))',
-1));
>  Aggregate  (cost=9505.13..9505.14 rows=1 width=0) (actual
> time=95.681..95.682 rows=1 loops=1)   ->  Bitmap Heap Scan on
> t_swath_metadata_old  (cost=506.77..9503.27 rows=745 width=0) (actual
> time=4.198..93.366 rows=7274 loops=1)
>          Recheck Cond: (swath_bounding &&
>
'010300000001000000050000000000000000315A402F127C4A8C8343C000000000E06960402
F127C4A8C8343C000000000E
>
0696040BD0E48C6526A2FC00000000000315A40BD0E48C6526A2FC00000000000315A402F127
C4A8C8343C0'::geometry)
>          Filter: (quicklook AND _st_intersects(swath_bounding,
>
'010300000001000000050000000000000000315A402F127C4A8C8343C000000000E06960402
F12
>
7C4A8C8343C000000000E0696040BD0E48C6526A2FC00000000000315A40BD0E48C6526A2FC0
0000000000315A402F127C4A8C8343C0'::geometry))
>          ->  Bitmap Index Scan on t_swath_metadata_old_swath_bounding_key
>  (cost=0.00..506.58 rows=16840 width=0) (actual time=3.557..3.557 ro
> ws=9020 loops=1)
>                Index Cond: (swath_bounding &&
>
'010300000001000000050000000000000000315A402F127C4A8C8343C000000000E06960402
F127C4A8C8343C000000
>
000E0696040BD0E48C6526A2FC00000000000315A40BD0E48C6526A2FC00000000000315A402
F127C4A8C8343C0'::geometry)
>  Total runtime: 95.757 ms
> (7 rows)
>
> 3) Geography bounding box < <1s:
> wastac=# explain analyze  SELECT count(1) AS count_1
> wastac-# FROM wastac.t_swath_metadata
> wastac-# WHERE wastac.t_swath_metadata.quicklook = True
> wastac-#  AND swath_bounding &&
> ST_GeographyFromText('SRID=4326;POLYGON((104.765625
> -39.0277188402,131.30859375 -39.0277188402,131.30859375
> -15.7076627696,104.765625 -15.7076627696,104.765625 -39.0277188402))');
>  Aggregate  (cost=10948.03..10948.04 rows=1 width=0) (actual
> time=30.583..30.584 rows=1 loops=1)   ->  Bitmap Heap Scan on
> t_swath_metadata  (cost=506.38..10943.48 rows=1820 width=0) (actual
> time=8.884..27.786 rows=9806 loops=1)
>          Recheck Cond: (swath_bounding &&
>
'0103000020E610000001000000050000000000000000315A402F127C4A8C8343C000000000E
06960402F127C4A8C8343C00
>
0000000E0696040BD0E48C6526A2FC00000000000315A40BD0E48C6526A2FC00000000000315
A402F127C4A8C8343C0'::geography)
>          Filter: quicklook
>          ->  Bitmap Index Scan on t_swath_metadata_swath_bounding_key
>  (cost=0.00..505.92 rows=10348 width=0) (actual time=7.403..7.403 rows=1
> 4263 loops=1)
>                Index Cond: (swath_bounding &&
>
'0103000020E610000001000000050000000000000000315A402F127C4A8C8343C000000000E
06960402F127C4A8C834
>
3C000000000E0696040BD0E48C6526A2FC00000000000315A40BD0E48C6526A2FC0000000000
0315A402F127C4A8C8343C0'::geography)
>  Total runtime: 30.637 ms
> (7 rows)
>
>
> On 28 May 2010 16:31, Paragon Corporation < <mailto:lr at pcorp.us>
lr at pcorp.us> wrote:
>>
>> Okay I think the fix is a really simple one
>>
>> Change your ST_Intersects function to this and see if it behaves right
>>
>> CREATE OR REPLACE FUNCTION st_intersects(geography, geography)
>>   RETURNS boolean AS
>> 'SELECT $1 && $2 AND _ST_Distance($1, $2, 0.0, false) < 0.00001'
>>   LANGUAGE 'sql' IMMUTABLE
>>   COST 100;
>>
>>
>> It should no longer need the && help to use the index.
>>
>

> _______________________________________________
> postgis-users mailing list
>  <mailto:postgis-users at postgis.refractions.net>
postgis-users at postgis.refractions.net
>  <http://postgis.refractions.net/mailman/listinfo/postgis-users>
http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>
_______________________________________________
postgis-users mailing list
 <mailto:postgis-users at postgis.refractions.net>
postgis-users at postgis.refractions.net
 <http://postgis.refractions.net/mailman/listinfo/postgis-users>
http://postgis.refractions.net/mailman/listinfo/postgis-users



_______________________________________________
postgis-users mailing list
postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users


-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20100531/f2dbee5d/attachment.html>