[postgis-users] No index usage on geography query plan?
Paul Ramsey
pramsey at cleverelephant.ca
Mon May 31 18:19:36 PDT 2010
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 <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 <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: postgis-users-bounces at postgis.refractions.net
> > [mailto: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.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 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
> > postgis-users at postgis.refractions.net
> > 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
>
> _______________________________________________
> 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/afbdcb2a/attachment.html>
More information about the postgis-users
mailing list