Hello,
I want to report that I have now solved the challenges I ran into using
KNN for US zipcode searching. I've found the new approach to not only be
viable, but to benchmark about 3x faster for our own real-world
application than the previous approach we used, involving
cube_distance() and
Mark Stosberg m...@summersault.com wrote:
Sample EXPLAIN output and query times are below.
Seq Scan on zipcodes (cost=0.00..1257.54 rows=41483 width=22)
(actual time=0.019..84.543 rows=41483 loops=1)
Index Scan using zipcodes_knn on zipcodes (cost=0.00..5365.93
rows=41483 width=22)
I thought the benefit of KNN was that you could retrieve the rows in
distance order, so that a query for the closest 20 locations (for
example) would be very fast. I wouldn't have expected it to be
helpful when you're selecting all the rows regardless of distance.
Kevin,
Thanks for the
* Mark Stosberg (m...@summersault.com) wrote:
Recommendations?
PostGIS, geometry columns, and UTM.. I'm not sure where they are wrt
adding KNN support, but it's something they've been anxious to have for
a while, so I expect support will come quickly.
Thanks,
Stephen
PostGIS, geometry columns, and UTM.. I'm not sure where they are wrt
adding KNN support, but it's something they've been anxious to have for
a while, so I expect support will come quickly.
I've looked into this a little more.
One approach seems to be to project the lat/long pairs on to a
On 17.02.2011 17:20, Mark Stosberg wrote:
I thought the benefit of KNN was that you could retrieve the rows in
distance order, so that a query for the closest 20 locations (for
example) would be very fast. I wouldn't have expected it to be
helpful when you're selecting all the rows regardless
I tried again to use KNN for a real-world query, and I was able to get
it to add an approximately 6x speed-up vs the cube search or
earthdistance methods ( from 300 ms to 50ms ).
I had to make some notable changes for the KNN index to be considered.
- Of course, I had to switch to using basic
Heikki Linnakangas heikki.linnakan...@enterprisedb.com writes:
The existing opclasses only support distance-to-a-point, but I believe
the KNN gist code is flexible enough that it could be used for distance
to the edge of a shape as well. Someone just needs to write the
operators and support
Mark Stosberg m...@summersault.com writes:
- The query planner didn't like it when the ORDER BY referred to a
column value instead of a static value, even when I believe it should
know that the column value never changes. See this pseudo-query where
we look-up the coordinates for 90210
Mark,
we investigating pgsphere http://pgsphere.projects.postgresql.org/, if we could
add KNN support.
Oleg
On Thu, 17 Feb 2011, Mark Stosberg wrote:
I thought the benefit of KNN was that you could retrieve the rows in
distance order, so that a query for the closest 20 locations (for
On 02/17/2011 03:17 PM, Oleg Bartunov wrote:
Mark,
we investigating pgsphere http://pgsphere.projects.postgresql.org/, if
we could add KNN support.
Great, thanks Oleg.
I'll be happy to test it when something is ready.
Mark
--
Sent via pgsql-performance mailing list
On Thu, Feb 17, 2011 at 11:17 AM, Tom Lane t...@sss.pgh.pa.us wrote:
Mark Stosberg m...@summersault.com writes:
- The query planner didn't like it when the ORDER BY referred to a
column value instead of a static value, even when I believe it should
know that the column value never changes.
12 matches
Mail list logo