| From: | "Anton Belyaev" <anton(dot)belyaev(at)gmail(dot)com> |
|---|---|
| To: | pgsql-general(at)postgresql(dot)org |
| Subject: | R-tree, order by, limit |
| Date: | 2008-09-21 13:23:48 |
| Message-ID: | d7e834b0809210623h7c2b3b55i1def9eebad6f7071@mail.gmail.com |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-general |
Hello,
I am implementing a map application. There are towns with altitude,
longitude and population.
One of the tasks is to be able to query N biggest (by population)
towns within a rectangle.
Something like (maybe the syntax in not quite right, but the idea is obvious):
SELECT * FROM towns where alt1 <= alt <= alt2 AND long1 <= long <=
long2 ORDER BY population LIMIT 10;
If I create an R-tree index on coordinates (alt, long) this will speed
up the query significantly. But it is still far from optimal: Despite
we need only 10 biggest towns, all towns in the rectangle specified
will be examined.
What if we include population into R-tree index? This index will
handle a 3D space with coordinates (alt, long, population).
Will this 3D index perform better than that 2D index?
In fact, I lack some details on how Postges handles ORDER_BY and LIMIT
inside R-tree indexes.
Extensive answers and links are appreciated.
Thanks.
Anton.
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Volkan YAZICI | 2008-09-21 13:57:00 | Re: R-tree, order by, limit |
| Previous Message | Sven Marcel Buchholz | 2008-09-21 09:52:44 | Re: PDF Documentation for 8.3? |