Re: [PERFORM] gist indexes for distance calculations

On 2010-09-30 20:33, Marcelo Zabani wrote: If you can also pinpoint me to where I can find this sort of information (index utilization and planning, performance tuning), I'd be very grateful. Thank you already, Isn't this what the knngist patches are for? https://commitfest.postgresql.org/a

Re: [PERFORM] gist indexes for distance calculations

> > Thanks a lot for all the help, if you can lead me to any docs/articles, > I'll gladly read them. I found this: http://en.wikipedia.org/wiki/R-tree Looks like what Tom was talking about, ja? Karim

Re: [PERFORM] gist indexes for distance calculations

So let me see if I understand, when searching for everyone within radius "r" of point (a,b), the gist index will be used like this: * C is the circle centered on (a,b) of radius "r" 1. Traverse down the tree, starting at the root. Only go down nodes whose bounding-box has a non-empty intersection

Re: [PERFORM] Memory usage - indexes

On 30/09/10 01:09, Tobias Brox wrote: With the most popular trans type it chose another plan and it took more than 3s (totally unacceptable): Try tweeking effective_cache_size up a bit and see what happens - I've found these bitmap plans to be sensitive to it sometimes. regards Mark

Re: [PERFORM] gist indexes for distance calculations

Marcelo Zabani writes: > CREATE INDEX ON places USING gist (circle(coordinates, 1)); > I'd like to know how this index works, though, as it seems to me the only > way to have this kind of index to work is to calculate the distance of every > point in a square of sides 2*1=2 units centered on (a,

[PERFORM] gist indexes for distance calculations

Hi everyone. I have a question, and it's well beyond me to even speculate about the inner workings of postgresql on this. I have a "places" table, and a "coordinates" column, of type POINT. If I want to find every place within, for example, a distance of 1 unit from an arbitrary point, I'll do:

Re: [PERFORM] turn off caching for performance test

Hi, Sorry for the late answer. I found the query i was looking for in the log (duration) and could prove that the seqscan is faster if the data were not cached. This particular one was 22% faster. It is "a query which will get turned into a nested loop index scan for a lot of rows, on a huge table

Re: [PERFORM] postgresql-9.0 Windows service stops after database transaction

Hi all Looks like Craig is right in suspecting that the PostGIS installation was causing the problem. I did a full reinstall of Postgres and ran it without PostGIS with no problems. For those interested, the bug has been noted on the PostGIS bugs list but yet to have a fix. http://trac.osgeo.or