[postgis-users] No index usage on geography query plan?
Paul Ramsey
pramsey at opengeo.org
Mon May 31 14:00:06 PDT 2010
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
>
>
More information about the postgis-users
mailing list