Re: [PERFORM] application of KNN code to US zipcode searches?

2011-10-29 Thread Mark Stosberg
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

Re: [PERFORM] application of KNN code to US zipcode searches?

2011-02-17 Thread Kevin Grittner
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)

Re: [PERFORM] application of KNN code to US zipcode searches?

2011-02-17 Thread Mark Stosberg
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

Re: [PERFORM] application of KNN code to US zipcode searches?

2011-02-17 Thread Stephen Frost
* 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

Re: [PERFORM] application of KNN code to US zipcode searches?

2011-02-17 Thread Mark Stosberg
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

Re: [PERFORM] application of KNN code to US zipcode searches?

2011-02-17 Thread Heikki Linnakangas
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

Re: [PERFORM] application of KNN code to US zipcode searches?

2011-02-17 Thread Mark Stosberg
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

Re: [PERFORM] application of KNN code to US zipcode searches?

2011-02-17 Thread Tom Lane
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

Re: [PERFORM] application of KNN code to US zipcode searches?

2011-02-17 Thread Tom Lane
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

Re: [PERFORM] application of KNN code to US zipcode searches?

2011-02-17 Thread Oleg Bartunov
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

Re: [PERFORM] application of KNN code to US zipcode searches?

2011-02-17 Thread Mark Stosberg
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

Re: [PERFORM] application of KNN code to US zipcode searches?

2011-02-17 Thread bricklen
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.