Пространство и время: применение cube и btree_gist

Привет

Хочу поделиться своим недавним опытом оптимизации работы с геометрическими данными в postgresql. На практике это решение показало очень хороший результат.

Допустим есть таблица для журналирования геоданных geodata_log, содержащая местоположение объекта obj_point типа point и время фиксации obj_created типа timestamp. По ней очень часто выполняется запрос получения самого "свежего" объекта в заданной области:

EXPLAIN ANALYZE
SELECT * FROM
    geodata_log
WHERE
    box(obj_point, obj_point) <@ box(point(50.857639595549, 30.337280273438), point(55.450349029297, 38.715576171875)) 
ORDER BY
    obj_created 
LIMIT 1;

Таблица достаточно интенсивная, т.е. мы с уверенностью можем сказать, что за последние 3 месяца туда добавлялась хотя бы одна запись. Соответственно сразу имеет смысл ограничиться по времени фиксации:

EXPLAIN ANALYZE
SELECT * FROM
    geodata_log
WHERE
    box(obj_point, obj_point) <@ box(point(50.857639595549, 30.337280273438), point(55.450349029297, 38.715576171875))
    AND obj_created > '2008-07-04'
ORDER BY
    obj_created
LIMIT 1;

Ситуацию без индексов я не рассматриваю в силу очевидности появления плохого плана. И так, создаём индекс по времени фиксации и местоположению:

CREATE INDEX i_geodata_log__created_point
 ON geodata_log
 USING gist
 (obj_created, box(obj_point, obj_point));

И видим ошибку:

ERROR: data type timestamp with time zone has no default operator class for access method "gist"
HINT: You must specify an operator class for the index or define a default operator class for the data type.

Подобное сообщением мы увидим если попробуем создать аналогичный btree индекс. Проблема в том, что для основных типов int4, timestamp и т.п. изначально поддерживаются только btree, а для геометрических типов только gist индексы.

Создадим отдельный индекс по местоположению:

CREATE INDEX i_geodata_log__point
 ON geodata_log
 USING gist
 (box(obj_point, obj_point));

План запроса:

Limit  (cost=256.03..256.04 rows=1 width=570) (actual time=17.081..17.082 rows=1 loops=1)
  ->  Sort  (cost=256.03..256.04 rows=1 width=570) (actual time=17.079..17.079 rows=1 loops=1)
        Sort Key: obj_created
        Sort Method:  top-N heapsort  Memory: 17kB
        ->  Bitmap Heap Scan on geodata_log  (cost=4.79..256.02 rows=1 width=570) (actual time=9.903..15.112 rows=829 loops=1)
              Recheck Cond: (box(obj_point, obj_point) <@ '(55.450349029297,38.715576171875),(50.857639595549,30.337280273438)'::box)
              Filter: (obj_created > '2008-07-04 00:00:00+04'::timestamp with time zone)
              ->  Bitmap Index Scan on i_geodata_log__point  (cost=0.00..4.79 rows=67 width=0) (actual time=9.231..9.231 rows=2362 loops=1)
                    Index Cond: (box(obj_point, obj_point) <@ '(55.450349029297,38.715576171875),(50.857639595549,30.337280273438)'::box
Total runtime: 17.184 ms

И по времени фиксации:

CREATE INDEX i_geodata_log__obj_created
 ON geodata_log
 USING btree
 (obj_created);

План:

Limit  (cost=40.44..40.45 rows=1 width=570) (actual time=15.288..15.288 rows=1 loops=1)
 ->  Sort  (cost=40.44..40.45 rows=1 width=570) (actual time=15.285..15.285 rows=1 loops=1)
       Sort Key: obj_created
       Sort Method:  top-N heapsort  Memory: 17kB
       ->  Bitmap Heap Scan on geodata_log  (cost=36.42..40.43 rows=1 width=570) (actual time=10.909..13.297 rows=829 loops=1)
             Recheck Cond: ((box(obj_point, obj_point) <@ '(55.450349029297,38.715576171875),(50.857639595549,30.337280273438)'::box) AND (obj_created > '2008-07-04 00:00:00+04'::timestamp with time zone))
             ->  BitmapAnd  (cost=36.42..36.42 rows=1 width=0) (actual time=10.751..10.751 rows=0 loops=1)
                   ->  Bitmap Index Scan on i_geodata_log__point  (cost=0.00..4.79 rows=67 width=0) (actual time=9.235..9.235 rows=2362 loops=1)
                         Index Cond: (box(obj_point, obj_point) <@ '(55.450349029297,38.715576171875),(50.857639595549,30.337280273438)'::box)
                   ->  Bitmap Index Scan on i_geodata_log__obj_created  (cost=0.00..31.38 rows=1483 width=0) (actual time=0.996..0.996 rows=3857 loops=1)
                         Index Cond: (obj_created > '2008-07-04 00:00:00+04'::timestamp with time zone)
Total runtime: 15.392 ms

Не смотря на индексные сканирования общая картина совсем не радует. Безвыходная ситуация? Нет.

Заглянув в стандартный набор сontrib'ов можно найти 2 интересных расширения
1. cube - добавляет тип cube - многомерный куб чисел с плавающей точкой
2. btree_gist - добавляет реализацию gist для int2, int4, timestamp и т.п.

cube может помочь ситуации, если представить наше пространство время тройками типа float (latitude, longitude, seconds_since_epoch), btree_gist просто позволит сделать смешанный индекс. Устанавливаем оба расширения, осталось проверить что быстрее.

Для cube добавим колонку area_time типа cube:

ALTER TABLE geodata_log
  ADD COLUMN area_time cube;

Заполним её нашими точечными кубами (obj_point[0], obj_point[1], extract(epoch from obj_created)):

UPDATE geodata_log SET
   area_time = cube(ARRAY[obj_point[0], obj_point[1], extract(epoch from obj_created)],
                    ARRAY[obj_point[0], obj_point[1], extract(epoch from obj_created)]);

И создадим индекс:

CREATE INDEX i_geodata_log__area_time
 ON geodata_log
 USING gist
 (area_time);

Немного изменяем запрос и получаем план:

EXPLAIN ANALYZE
SELECT  * FROM
   geodata_log
WHERE
   area_time <@ cube(ARRAY[50.857639595549, 30.337280273438, extract(epoch from '2008-07-04'::timestamp)],                               
                     ARRAY[55.450349029297, 38.715576171875, extract(epoch from now()::timestamp)]) 
ORDER BY
   obj_created 
LIMIT 1;  

Limit  (cost=265.36..265.36 rows=1 width=614) (actual time=5.312..5.313 rows=1 loops=1)   
 ->  Sort  (cost=265.36..265.52 rows=67 width=614) (actual time=5.310..5.310 rows=1 loops=1)
       Sort Key: obj_created
       Sort Method:  top-N heapsort  Memory: 17kB
       ->  Bitmap Heap Scan on geodata_log  (cost=8.83..265.02 rows=67 width=614) (actual time=0.794..3.190 rows=829 loops=1)
             Recheck Cond: (area_time <@ cube('{50.857639595549,30.337280273438,1215115200}'::double precision[], ARRAY[55.450349029297::double precision, 38.715576171875::double precision, date_part('epoch'::text, (now())::timestamp without time zone)]))
             ->  Bitmap Index Scan on i_geodata_log__area_time  (cost=0.00..8.81 rows=67 width=0) (actual time=0.682..0.682 rows=829 loops=1)
                   Index Cond: (area_time <@ cube('{50.857639595549,30.337280273438,1215115200}'::double precision[], ARRAY[55.450349029297::double precision, 38.715576171875::double precision, date_part('epoch'::text, (now())::timestamp without time zone)])) 
Total runtime: 5.420 ms

Уже лучше, определённо. Теперь пробуем btree_gist:

CREATE INDEX i_geodata_log__created_point
 ON geodata_log
 USING gist
 (obj_created, box(obj_point, obj_point));

EXPLAIN ANALYZE
SELECT  * FROM
   geodata_log
WHERE
   box(obj_point, obj_point) <@ box(point(50.857639595549, 30.337280273438), point(55.450349029297, 38.715576171875))     
   AND obj_created > '2008-07-04'
ORDER BY
   obj_created
LIMIT 1;

Limit  (cost=8.31..8.32 rows=1 width=570) (actual time=5.092..5.093 rows=1 loops=1)
 ->  Sort  (cost=8.31..8.32 rows=1 width=570) (actual time=5.092..5.092 rows=1 loops=1)
       Sort Key: obj_created
       Sort Method:  top-N heapsort  Memory: 17kB
       ->  Index Scan using i_geodata_log__created_point on geodata_log  (cost=0.00..8.30 rows=1 width=570) (actual time=0.076..3.408 rows=829 loops=1)
             Index Cond: ((obj_created > '2008-07-04 00:00:00+04'::timestamp with time zone) AND (box(obj_point, obj_point) <@ '(55.450349029297,38.715576171875),(50.857639595549,30.337280273438)'::box)) 
Total runtime: 5.170 ms

Совсем хорошо.

Сделав несколько испытаний в различных ситуациях, я отметил, что разницы в производительности между cube и btree_gist на моих объёмах данных практически нет, но т.к. cube потребует создание дополнительной колонки и триггера, который будет её обновлять, я выбрал btree_gist.

Что лучше выбрать вам будет зависит от вашей ситуации.

p.s. В ближайшее время опубликую пост "Отделяем мух от котлет: проблема активных и архивных данных", будет интересно, обещаю :)

С уважением,
Серегей Коноплёв