Блог

У меня есть таблица со geometry столбцом.

У меня есть 2 индекса в этом столбце:

 create index idg1 on tbl using gist(geom)

create index idg2 on tbl using gist(st_geomfromewkb((geom)::bytea))
  

У меня много запросов, использующих поле geom ( geometry ).

1. Какой индекс используется? (когда и почему)
2. Если в одном столбце есть два индекса (как я показываю здесь), может select queries ли запускаться медленнее, чем определять только один индекс в столбце?

Использование индекса зависит от того, как был определен индекс и как вызывается запрос. Если вы SELECT <cols> FROM tbl WHERE geom = <some_value> , то вы будете использовать idg1 индекс. Если вы SELECT <cols> FROM tabl WHERE st_geomfromewkb(geom) = <some_value> , то вы будете использовать idg2 индекс.

Хороший способ узнать, какой индекс будет использоваться для конкретного запроса, — это вызвать запрос с EXPLAIN помощью ( EXPLAIN SELECT <cols> FROM tbl WHERE geom = <some_value> т. Е.) — это распечатает план запроса, какие методы доступа, какие индексы, какие объединения и т.д. будет использоваться.

Что касается вашего вопроса о производительности, SELECT запросы могут выполняться медленнее, поскольку на этапе планирования запросов необходимо учитывать больше индексов. С точки зрения выполнения заданного плана SELECT запроса запрос не будет выполняться медленнее, потому что к тому времени план запроса был установлен и решение о том, какой индекс использовать, было принято.

Вы, безусловно, почувствуете влияние на производительность INSERT / UPDATE / DELETE таблицы, так как все индексы необходимо будет обновлять с учетом изменений в таблице. Таким образом, на диске будет дополнительная активность ввода-вывода для распространения изменений, замедляя работу базы данных, особенно в масштабе.

Какой индекс используется, зависит от запроса.

Любой запрос, имеющий

 WHERE geom amp;amp; '...'::geometry
  

или

 WHERE st_intersects(geom, '...'::geometry)
  

или аналогичный будет использовать первый индекс.

Второй индекс будет использоваться только для запросов, в которых есть выражение st_geomfromewkb((geom)::bytea) .

Это совершенно бесполезно: оно преобразует геометрию в формат EWKB и обратно. Вы должны найти и переписать все запросы, которые имеют эту странную конструкцию, затем вы должны удалить этот индекс.

Наличие двух индексов в одном столбце не приводит к значительному замедлению ваших запросов (планирование займет немного больше времени, но я сомневаюсь, что вы сможете это измерить). Однако при каждом изменении данных у вас будет снижение производительности, что займет почти в два раза больше времени, чем при использовании одного индекса.