Postgres GIST против индекса Btree - программирование

Следуя моему предыдущему вопросу по этой теме Postgres, объединяющему несколько индексов:

У меня есть следующая таблица в Postgres 9.2 (с postgis):

CREATE TABLE updates (
    update_id character varying(50) NOT NULL,
    coords geography(Point,4326) NOT NULL,
    user_id character varying(50) NOT NULL,
    created_at timestamp without time zone NOT NULL
);

И я выполняю следующий запрос в таблице:

select * 
from updates 
where ST_DWithin(coords, ST_MakePoint(-126.4, 45.32)::geography, 30000) 
and user_id='3212312' 
order by created_at desc
limit 60

Итак, учитывая, какой индекс я должен использовать для (coords + user_id), GIST или BTree?

CREATE INDEX ix_coords_user_id ON updates USING GIST (coords, user_id);

ИЛИ

CREATE INDEX ix_coords_user_id ON updates (coords, user_id);

Я читал, что BTree работает лучше, чем GIST, но я вынужден использовать GIST, поскольку я использую поле географии postgis??

Ответ 1

Вы должны использовать GiST, если хотите использовать какой-либо индексный метод, отличный от обычных индексов дерева b (или хеш-индексов, но они действительно не должны использоваться). Индексы PostGIS требуют GiST.

Индексы B-дерева могут использоваться только для основных операций, связанных с равенством или упорядочением, например =, <, <=, >, >=, <>, BETWEEN и IN. Хотя вы можете создать индекс b-дерева для объекта геометрии (точки, региона и т.д.), Его можно использовать только для равенства, так как упорядочение сравнений типа > обычно не имеет смысла для таких объектов. Индекс GiST необходим для поддержки более сложных и общих сравнений, таких как "содержит", "пересекает" и т.д.

Вы можете использовать расширение btree_gist, чтобы включить индексирование b-дерева для GiST. Это значительно медленнее, чем обычные индексы b-tree, но позволяет создать индекс с несколькими столбцами, который содержит как типы GiST, так и обычные типы, такие как text, integer и т.д.

В этих ситуациях вам действительно нужно использовать explain analyze (explain.depesz.com для этого), чтобы изучить, как Pg использует различные индексы и комбинации индексов, которые вы Создайте. Попробуйте разные порядки столбцов в многоколоночных индексах и посмотрите, эффективны ли два или более отдельных индекса.

Я сильно подозреваю, что в этом случае вы получите наилучшие результаты с помощью многоколоночного индекса GiST, но я бы попробовал несколько разных комбинаций индексов и порядков столбцов индекса.