Skip site navigation (1) Skip section navigation (2)

gist indexes for distance calculations

From: Marcelo Zabani <mzabani(at)gmail(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: gist indexes for distance calculations
Date: 2010-09-30 18:33:03
Message-ID: AANLkTikvpQS+1q3HBrVCgCjRF+2-LPGzmVHQO+EJk3QG@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
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:

CREATE INDEX ON places USING gist (circle(coordinates, 1));

And then I'll fetch the desired rows like this:

SELECT * FROM places WHERE circle(coordinates, 1) @> circle('(a,b)', 0);
(where (a,b) is an arbitrary point)

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, b).

So, am I wrong to think it works like that? If it does work like that, could
I have instead two columns of type FLOAT (xcoordinate and ycoordinate) and
create traditional b-tree indexes on both of these, and then do something
like:

SELECT * FROM places WHERE xcoordinate >= (a-1) AND xcoordinate <= (a+1) AND
ycoordinate >= (b-1) AND ycoordinate <= (b+1) And
SQRT(POW(a-xcoordinate,2)+POW(b-ycoordinate,2))<=1;

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,
Marcelo Zabani.

Responses

pgsql-performance by date

Next:From: Tom LaneDate: 2010-09-30 19:16:36
Subject: Re: gist indexes for distance calculations
Previous:From: Willy-Bas LoosDate: 2010-09-30 16:41:55
Subject: Re: turn off caching for performance test

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group