[prev in list] [next in list] [prev in thread] [next in thread]
List: postgis-users
Subject: Re: [postgis-users] No index usage on geography query plan?
From: Paul Ramsey <pramsey () opengeo ! org>
Date: 2010-05-31 21:00:06
Message-ID: AANLkTinnLwyMV8gOJhsD0NEHKyNdibs58hE0_HI-dMAl () mail ! gmail ! com
[Download RAW message or body]
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 <lr@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-bounces@postgis.refractions.net
> [mailto:postgis-users-bounces@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 <lr@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
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic