[postgis-devel] knn test - first result

[maplabs at light42.com]

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