Re: R-tree, order by, limit

On Sun, Sep 21, 2008 at 06:17:39PM +0400, Anton Belyaev wrote:
> Geometry types and functions use R-tree indexes anyways.
>
> I can rephrase the query using geometry language of Postgres:
> SELECT * FROM towns WHERE towns.coordinates <@ box(alt1, long1, alt2,
> long2) ORDER BY population LIMIT 10;
>
> And the questions about population remain the same:
> How to avoid examination of all the towns in the rectangle knowing
> that we need only 10 biggest?

I don't know if it solves your problem, but you should be able to do a
multi-column GiST index with both the position data and the population
data in it. However, I'm unsure if postgresql will correctly use the
index to solve the order by...

Have a nice day,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> Please line up in a tree and maintain the heap invariant while
> boarding. Thank you for flying nlogn airlines.