Re: R-tree, order by, limit

2008/9/21 Volkan YAZICI <yazicivo@ttmail.com>:
> On Sun, 21 Sep 2008, "Anton Belyaev" <anton.belyaev@gmail.com> writes:
>> SELECT * FROM towns where alt1 <= alt <= alt2 AND long1 <= long <=
>> long2 ORDER BY population LIMIT 10;
>
> You're absolutely on the wrong path. Don't try to implement a logic,
> that has been implemented by PostgreSQL in the most possibly efficient
> way in its bounds. See geographic data types[1] (e.g. box) and
> geographic functions[2] (e.g. @> a.k.a contains).
>
>
> Regards.
>
> [1] http://www.postgresql.org/docs/current/interactive/datatype-geometric.html
> [2] http://www.postgresql.org/docs/current/interactive/functions-geometry.html
>

Volkan,
Thanks you for your reply.
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?
Does population worth including into a (3D) point (In order to create
a 3D R-tree)? Does Postgres perform ODRER/LIMIT efficiently in this
case?

Thanks.
Anton.