Thomas Pöhler wrote:
I remember you said you were using nginx and php-fastcgi, how many web
server boxes do you have, and what are the specs ?
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
We perform over 1,000,000 searches each day for adoptable shelter pets
near your zipcode. We already have adequate performance for these
searches using the cube contrib, but the new KNN work in 9.1 seemed
like it might be a promising way to speed this up even further.
I installed PostgreSQL 9.1
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.
I think adding
UNION ALL SELECT 'postgres version', version();
might be a good thing.
On Wed, Feb 16, 2011 at 9:55 AM, Greg Smith g...@2ndquadrant.com wrote:
Kevin Grittner wrote:
In fact, I wonder whether we shouldn't leave a couple items you've
excluded, since they are sometimes germane
Scott, are you really moving that much data through memory, 70-80GB/sec is the
limit of the new intel 7500 series in a best case scenario.
- John
-Original Message-
From: pgsql-performance-ow...@postgresql.org
[mailto:pgsql-performance-ow...@postgresql.org] On Behalf Of Scott Marlowe
15 matches
Mail list logo