[postgis-users] No index usage on geography query plan?
Nicholas Bower
nick at petangent.net
Mon May 31 19:46:56 PDT 2010
I understand your explanation now - it's why our alternative queries over a
pre-calculated 20km grid from overlaying the large boundary approached
geometry in spatial performance (but then we incur far worse overall
performance because of the sheer number of features in the query).
The reason we are querying geographies instead of geometries is because we
have large-scale global coverages that cross IDL and get warped near poles.
To me, querying large scale features is the whole reason for geography, and
so ideally my scenario should reflect best case performance not worst for
geography.
Otherwise if I had small features (minimal warping - eg my 20km grid), I'd
just continue as I have for years and use geometry operators and handle IDL
wrapping by adding 360 onto any negative longitude. Not fancy but fast.
On 1 June 2010 12:27, Paragon Corporation <lr at pcorp.us> wrote:
> 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 < <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 < <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: <postgis-users-bounces at postgis.refractions.net>
>> postgis-users-bounces at postgis.refractions.net
>> > [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.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>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>
>> 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>
>> 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
>
>
> _______________________________________________
> 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/20100601/ed66464a/attachment.html>
More information about the postgis-users
mailing list