Содержание

5.1. Маленькие таблицы больших геометрий

5.1.1. Описание проблемы

5.1.2. Как обойти проблему

5.2. CLUSTER-изация геометрических индексов

5.3. Избегайте изменений размерности

5.1. Маленькие таблицы больших геометрий

5.1.1. Описание проблемы

Текущие версии PostgreSQL (включая 8.0) имеют слабый оптимизатор запросов для таблиц TOAST. Таблицы TOAST являются разновидностью "расширения" используемые для хранения больших значений (в смысле размера данных), которых нет в обычных данных (например длинные тексты, изображения или сложные геометрии, с множеством вершин). Подробную информацию можно получить здесь http://www.postgresql.org/docs/8.0/static/storage-toast.html.

Проблема появляется, если у вас таблица с большими геометриями, но с небольшим числом строк (как, например, в таблице, содержащей границы всех европейских стран в высоком разрешении). Такая таблица сама по себе мала, но использует много места в TOAST. В нашем примере таблица имеет всего 80 строк и всего 3 страницы данных, но таблица TOAST использует 8225 страниц.

Теперь рассмотрим запрос, в котором вы используете геометрический оператор && для поиска границы, которой соответствует только очень немногие из этих строк. Оптимизатор запроса видит, что таблица имеет всего 3 страницы и 80 строк. Он вычисляет, что последовательное сканирование по всей маленькой таблице быстрее использования индекса. Поэтому он принимает решение игнорировать индекс GIST. Обычно такое суждение правильно. Но не в нашем случае, так как оператор && будет получать с диска каждую геометрию для сравнения границ и, таким образом, считает все страницы TOAST.

Посмотреть, не в этом ли причина ошибки, можно с помощью команды postgresql "EXPLAIN ANALYZE". Более подробную информацию и технические детали вы можете прочитать в ветке листа рассылки postgres: http://archives.postgresql.org/pgsql-performance/2005-02/msg00030.php

5.1.2. Как обойти проблему

Пользователи PostgreSQL пытаются найти решение с помощью создания запросов, учитывающих TOAST. Вот два способа:

Первый способ - насильно заставить планировщик запросов использовать индекс. Перед выполнением запроса сообщите серверу "SET enable_seqscan TO off;". Эта команда насильно отменяет план запроса с возможным полным сканированием. Теперь индекс GIST будет использоваться как обычно. Но этот флаг будет действовать в течение всего коннекта и может стать причиной того, что планировщик запросов будет неверно работать в других случаях. Поэтому рекомендуем выполнить "SET enable_seqscan TO on;" после запроса.

Второй способ заключается в том, чтобы выполнить сканирование с той скоростью, на которую расчитывает планировщик. Этого можно добиться, создав дополнительный "кэширующий" столбец bbox, равносильный исходному. В нашем примере команды могут быть такими:

SELECT addGeometryColumn('myschema','mytable','bbox','4326','GEOMETRY','2'); UPDATE mytable set bbox = Envelope(Force_2d(the_geom));

Теперь изменим запрос, использующий оператор && для bbox вместо geom_column:

SELECT geom_column FROM mytable WHERE bbox && ST_SetSRID('BOX3D(0 0,1 1)'::box3d,4326);

Разумеется, что если вы изменяете или добавляете строки в mytable, вы должны синхронизировать bbox. Наиболее простой способ делать это - триггеры. Кроме того, вы можете изменить свое приложение так чтобы оно следило за корректностью столбца bbox или выполняло указанный выше запрос UPDATE после каждой модификации.

5.2. CLUSTER-изация геометрических индексов

Если таблица, обычно, только читается, и большинство запросов к ней используют единственный индекс, то к ней можно применить команду CLUSTER, предлагаемую PostgreSQL. Эта команда физически сортирует все строки данных в порядке, обусловленном индексом, что дает два преимущества в производительности. Во-первых, резко сокращается число обращений к данным таблицы при сканировании по диапазону индекса. Во-вторых, если вы чаще работаете с каким-то небольшим интервалом индексов, вы будете иметь более эффективное кэширование, потому что строки данных распределены между немногими страницами данных. (Рекомендуем прочитать документацию по команде CLUSTER в мануале PostgreSQL.)

Однако, в настоящее время PostgreSQL не позволяет кластеризовать индексы PostGIS GIST, так как индексы GIST просто игнорируют значения NULL. При попытке вы получите соббщение об ошибке вроде этого:

lwgeom=# CLUSTER my_geom_index ON my_table; ERROR: cannot cluster when index access method does not handle null values HINT: You may be able to work around this by marking column "the_geom" NOT NULL.

Если вы получили такое сообщение HINT, вы все же можете заставить это выражение работать, добавив в таблицу ограничение "not null":

lwgeom=# ALTER TABLE my_table ALTER COLUMN the_geom SET not null; ALTER TABLE

Разумеется, это не сработает, если вам необходимы значения NULL в геометрическом столбце. Учтите, что для создания ограничения вы должны использовать описанный выше метод. Использование ограничения CHECK, вроде такого: "ALTER TABLE blubb ADD CHECK (geometry is not null);", - работать не будет.

5.3. Избегайте изменений размерности

Иногда таблица содержит 3D- или 4D-данные, но для совместимости с OpenGIS следует всегда обращаться к ним с помощью функций asText() или asBinary(), которые возвращают только 2D-геометрии. Они делают это с помощью вызовов функции force_2d(), которая влечет существенные накладные расходы для больших геометрий. Во избежание этих накладных расходов стоит удалить эти лишние измерения раз и навсегда:

UPDATE mytable SET the_geom = force_2d(the_geom); VACUUM FULL ANALYZE mytable;

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

В случае больших таблиц будет разумно осуществлять UPDATE небольшими порциями, ограничив UPDATE выражением WHERE с использованием первичного ключа или другого выполнимого условия. И запускать "VACUUM;" между UPDATE. Это существенно снижает потребность во временном дисковом пространстве. Кроме того, если вы имеете геометрии смешанной размерности, ограничение UPDATE посредством "WHERE dimension(the_geom)>2" приведет к неперезаписи геометрий, которые уже являются 2D.

Последнее обновление: August 14 2008
(Наверх)