R-tree, order by, limit
От | Anton Belyaev |
---|---|
Тема | R-tree, order by, limit |
Дата | |
Msg-id | d7e834b0809210623h7c2b3b55i1def9eebad6f7071@mail.gmail.com обсуждение исходный текст |
Ответы |
Re: R-tree, order by, limit
Re: R-tree, order by, limit |
Список | 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.
В списке pgsql-general по дате отправления: