Re: R-tree, order by, limit

2008/9/21 Martijn van Oosterhout <kleptog@svana.org>:
> 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...

Martijn, thanks for you reply.
Implementing a 3D R-tree index in Postgres is only possible via
implementation of GiST interface. At least, this is the only approach
I consider, because implementing a brand new index access method
requires much more than just classic R-tree implementation.
So, yes, question remains the same, but a bit updated:
How efficiently Postgres handles ORDER BY + LIMIT when using GiST?
(Particularly, when an R-tree is implemented via GiST).

Anton.