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 <n...@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 <pram...@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 <l...@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: postgis-users-boun...@postgis.refractions.net
> [mailto:postgis-users-boun...@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.765625
> -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 &&
>
'0103000020E610000001000000050000000000000000315A402F127C4A8C8343C000000000E06960402F127C4A8C8343C00
>
0000000E0696040BD0E48C6526A2FC00000000000315A40BD0E48C6526A2FC00000000000315A402F127C4A8C8343C0'
::geography)
> Filter: (quicklook AND (_st_distance(swath_bounding,
>
'0103000020E610000001000000050000000000000000315A402F127C4A8C8343C000000000E0696
>
0402F127C4A8C8343C000000000E0696040BD0E48C6526A2FC00000000000315A40BD0E48C6526A2FC00000000000315A402F127C4A8C8343C0'
::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 &&
>
'0103000020E610000001000000050000000000000000315A402F127C4A8C8343C000000000E06960402F127C4A8C834
>
3C000000000E0696040BD0E48C6526A2FC00000000000315A40BD0E48C6526A2FC00000000000315A402F127C4A8C8343C0'
::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 &&
>
'010300000001000000050000000000000000315A402F127C4A8C8343C000000000E06960402F127C4A8C8343C000000000E
>
0696040BD0E48C6526A2FC00000000000315A40BD0E48C6526A2FC00000000000315A402F127C4A8C8343C0'
::geometry)
> Filter: (quicklook AND _st_intersects(swath_bounding,
>
'010300000001000000050000000000000000315A402F127C4A8C8343C000000000E06960402F12
>
7C4A8C8343C000000000E0696040BD0E48C6526A2FC00000000000315A40BD0E48C6526A2FC00000000000315A402F127C4A8C8343C0'
::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 &&
>
'010300000001000000050000000000000000315A402F127C4A8C8343C000000000E06960402F127C4A8C8343C000000
>
000E0696040BD0E48C6526A2FC00000000000315A40BD0E48C6526A2FC00000000000315A402F127C4A8C8343C0'
::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 &&
>
'0103000020E610000001000000050000000000000000315A402F127C4A8C8343C000000000E06960402F127C4A8C8343C00
>
0000000E0696040BD0E48C6526A2FC00000000000315A40BD0E48C6526A2FC00000000000315A402F127C4A8C8343C0'
::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 &&
>
'0103000020E610000001000000050000000000000000315A402F127C4A8C8343C000000000E06960402F127C4A8C834
>
3C000000000E0696040BD0E48C6526A2FC00000000000315A40BD0E48C6526A2FC00000000000315A402F127C4A8C8343C0'
::geography)
> Total runtime: 30.637 ms
> (7 rows)
>
>
> On 28 May 2010 16:31, Paragon Corporation <l...@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
> postgis-users@postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>
_______________________________________________
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
_______________________________________________
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
_______________________________________________
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users