Почему геометрия тормозит?

[hell0w0rd]

Мне в проекте надо немного работать с координатами. Сегодня делал фичу, связанную с поиском ближайших записей от определенной точки и нагуглил оператор <-> и собственно саму геометрию в постгресе.
Помимо ближайшей точки есть необходимость искать между двумя точками, на данный момент использую два between и индекс по координатам, решил потестить с box из двух точек и @> оператор.
https://gist.github.com/nkt/408a8e42ea558b288f61 - вот код
Собственно вопрос, почему таки between быстрее на 100-150мс?

EXPLAIN ANALYZE
SELECT *
FROM point_test
WHERE box(point(10, 10), point(90, 90)) @> coordinates;
                                                              QUERY PLAN                                                             
--------------------------------------------------------------------------------------------------------------------------------------

Bitmap Heap Scan on point_test  (cost=86.30..6228.15 rows=2308 width=36) (actual time=281.676..391.566 rows=639488 loops=1)
  Recheck Cond: ('(90,90),(10,10)'::box @> coordinates)
  Heap Blocks: exact=14703
  ->  Bitmap Index Scan on point_test_gist  (cost=0.00..85.72 rows=2308 width=0) (actual time=231.090..231.090 rows=1278976 loops=1)
        Index Cond: ('(90,90),(10,10)'::box @> coordinates)
Planning time: 0.112 ms
Execution time: 421.525 ms

EXPLAIN ANALYZE
SELECT *
FROM point_test
WHERE x BETWEEN 10 AND 90 AND y BETWEEN 10 AND 90;
                                                                    QUERY PLAN                                                                 
---------------------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on point_test  (cost=329.56..551.79 rows=58 width=36) (actual time=155.628..253.860 rows=639488 loops=1)
  Recheck Cond: ((x >= 10::double precision) AND (x <= 90::double precision) AND (y >= 10::double precision) AND (y <= 90::double precision))
  Heap Blocks: exact=8334
  ->  Bitmap Index Scan on point_test_btree  (cost=0.00..329.55 rows=58 width=0) (actual time=153.761..153.761 rows=639488 loops=1)
        Index Cond: ((x >= 10::double precision) AND (x <= 90::double precision) AND (y >= 10::double precision) AND (y <= 90::double precision))
Planning time: 0.140 ms
Execution time: 283.563 ms

 

[hell0w0rd]

У меня есть мысль, что сначала прогоняется первый between, а затем второй, отсюда и скорость. Чего вероятно, в силу реализации нельзя сделать с @>

 

[Sad Spirit]

На первый взгляд в EXPLAIN'ах есть принципиальное различие только в кол-ве проверяемых блоков heap после bitmap index scan: там, где запрос в 1,5 раза дольше выполняется, проверяется в 1,76 раз больше блоков. Я так понимаю, это особенность индексов gist, они lossy, то есть возвращают "лишние" ссылки на heap. А btree "лишних" ссылок не возвращает.

 

[hell0w0rd]

Sad Spirit, ура первый ответ!
У меня есть еще предположение, что дело в настройках базы. Я все прогонял на дефолтных настройках на простой vps. Сейчас прогнал на локалке, получил другие результаты:

EXPLAIN ANALYZE
SELECT *
FROM point_test                                      
WHERE x BETWEEN 10 AND 90 AND y BETWEEN 10 AND 90;
                                                                    QUERY PLAN                                                                  
---------------------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on point_test  (cost=155.43..252.84 rows=25 width=36) (actual time=118.821..199.945 rows=640039 loops=1)
  Recheck Cond: ((x >= 10::double precision) AND (x <= 90::double precision) AND (y >= 10::double precision) AND (y <= 90::double precision))
  Heap Blocks: exact=8334
  ->  Bitmap Index Scan on point_test_btree  (cost=0.00..155.43 rows=25 width=0) (actual time=117.573..117.573 rows=640039 loops=1)
        Index Cond: ((x >= 10::double precision) AND (x <= 90::double precision) AND (y >= 10::double precision) AND (y <= 90::double precision))
Planning time: 0.108 ms
Execution time: 234.830 ms

EXPLAIN ANALYZE
SELECT *
FROM point_test
WHERE box(point(10, 10), point(90, 90)) @> coordinates;
                                                            QUERY PLAN                                                          
-----------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on point_test  (cost=48.03..3187.41 rows=1000 width=36) (actual time=98.172..182.354 rows=640039 loops=1)
  Recheck Cond: ('(90,90),(10,10)'::box @> coordinates)
  Heap Blocks: exact=8334
  ->  Bitmap Index Scan on point_test_gist  (cost=0.00..47.78 rows=1000 width=0) (actual time=96.806..96.806 rows=640039 loops=1)
        Index Cond: ('(90,90),(10,10)'::box @> coordinates)
Planning time: 0.102 ms
Execution time: 218.434 ms

PS может есть вариант, что на vps индексы просто не влезли в память... Хотя там 8гб. В общем пошел смотреть настройки

 

[grigori]

по дефолту shared_buffers = 32MB, надо менять sysctl и postgresql.conf

 

[hell0w0rd]

Почитал про основные параметры, увеличил значения по умолчанию - в результате геометрия даже выигрывает по 30-50мс на этом запросе.
@grigori, @Sad Spirit спасибо!