[postgis-devel] knn test - first result
Nicklas Avén
nicklas.aven at jordogskog.no
Fri May 20 04:46:17 PDT 2011
I can't see how knn would help st_dwithin. The thing with knn is that
the index returns the result in order. In the case of st_dwithin the
index is just supposed to return all bbox-intersections (or expanded
bbox intersections) and then all geometries returned from the index
needs to be rechecked. It is no help for the recheck function to get the
results ordered.
The job for the recheck is also very small when you just get 4 rows as
result (if there isn't extreamly many points in the corners of the
expanded bounding box from your point).
>From your timings I would guess that in the slower case there is no
index used at all but in the faster case you have an ordinary index scan
(nothing to do with knn).
I am very bad in reading those explain analyze texts but I cannot see
any index scan in any of them. Are you sure you have copied the right
one for the fast example?
BTW, have you patched PostGIS 1.4 to compile it against PostgreSQL 9.1?
I ask because ticket 940 http://trac.osgeo.org/postgis/ticket/940#
should make it impossible otherwise if Paul didn't fix 1.4 too.
Regards
Nicklas
On Fri, 2011-05-20 at 01:45 -0400, maplabs at light42.com wrote:
> well, perhaps I have missed something..
> however, on a fresh debian6 vm, GEOS trunk, postgis trunk, postgresql 9.1b1 built from source
> I loaded some POINT ( with GIST index unchanged from the dump coming from 8.4 )
> and a proximity search "just worked".. 100x faster.. what needs to be done ?
>
> ================================================================================
>
> PostgreSQL 8.4.7 on x86_64-pc-linux-gnu, compiled by GCC gcc-4.4.real (Debian 4.4.5-8) 4.4.5, 64-bit
>
> vs
>
> PostgreSQL 9.1beta1 on i686-pc-linux-gnu, compiled by GCC gcc (Debian 4.4.5-8) 4.4.5, 32-bit
> ================================================================================
>
> data_acer=# select postgis_full_version();
> postgis_full_version
> -------------------------------------------------------------------------------------------
> POSTGIS="1.4.1SVN" GEOS="3.3.0-CAPI-1.7.0" PROJ="Rel. 4.7.1, 23 September 2009" USE_STATS
> (1 row)
>
> data_acer=# explain analyze select name from geo.us_locations where st_dwithin( thepoint_lonlat, st_geomfromewkt('SRID=4326;POINT( -112.0 38)'), 100 ) limit 10;
>
> QUERY PLAN
> ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> Limit (cost=0.00..82889.48 rows=3 width=18) (actual time=20.415..20.589 rows=10 loops=1)
> -> Seq Scan on us_locations (cost=0.00..82889.48 rows=3 width=18) (actual time=20.410..20.551 rows=10 loops=1)
> Filter: ((thepoint_lonlat && '0103000020E610000001000000050000000000000000806AC00000000000004FC00000000000806AC0000000000040614000000000000028C0000000000040614000000000000028C00000000000004FC00000000000806AC00000000000004FC0'::geometry) AND _st_dwithin(thepoint_lonlat, '0101000020E61000000000000000005CC00000000000004340'::geometry, 100::double precision) AND ('0101000020E61000000000000000005CC00000000000004340'::geometry && st_expand(thepoint_lonlat, 100::double precision)))
> Total runtime: 35.320 ms
> (4 rows)
>
> ##============= ====================== =========================== ====================
>
> dbb=# explain analyze select name from us_locations where st_dwithin( thepoint_lonlat, st_geomfromewkt('SRID=4326;POINT( -112.0 38)'), 100 ) limit 10;
>
>
> QUERY PLAN
>
>
> ---------------------------------------------------------------------------------------------------
> ---------------------------------------------------------------------------------------------------
> ---------------------------------------------------------------------------------------------------
> ---------------------------------------------------------------------------------------------------
> ---------------------------------------------------------------------------------------------
> Limit (cost=0.00..547761.04 rows=3 width=18) (actual time=0.038..0.217 rows=10 loops=1)
> -> Seq Scan on us_locations (cost=0.00..547761.04 rows=3 width=18) (actual time=0.027..0.114 r
> ows=10 loops=1)
> Filter: ((thepoint_lonlat && '0103000020E610000001000000050000000000000000806AC00000000000
> 004FC00000000000806AC0000000000040614000000000000028C0000000000040614000000000000028C00000000000004
> FC00000000000806AC00000000000004FC0'::geometry) AND ('0101000020E61000000000000000005CC000000000000
> 04340'::geometry && st_expand(thepoint_lonlat, 100::double precision)) AND _st_dwithin(thepoint_lon
> lat, '0101000020E61000000000000000005CC00000000000004340'::geometry, 100::double precision))
> Total runtime: 0.299 ms
> (4 rows)
>
> dbb=# select postgis_full_version();
> postgis_full_version
>
> ---------------------------------------------------------------------------------------------------
> ----------
> POSTGIS="2.0.0SVN" GEOS="3.3.0rc1-CAPI-1.7.0" PROJ="Rel. 4.7.1, 23 September 2009" LIBXML="2.7.8"
> USE_STATS
>
> ----
> Brian Hamlin
> planetwork.net
> OSGeo California Chapter
> (415) 717-4462 cell
>
> _______________________________________________
> postgis-devel mailing list
> postgis-devel at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-devel
>
More information about the postgis-devel
mailing list