Интернет, компьютеры, софт и прочий Hi-Tech

Подписаться через RSS2Email.ru
Руководство по PostGIS. Глава 5. Советы по производительности
Назад Вперед

Глава 5. Советы по производительности

Содержание

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));

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

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.


Назад Выше Вперед
4.8. Клиенты Java (JDBC) Начало Глава 6. Справочник PostGIS
Биржа долевых инвестиций SIMEX.

Последнее редактирование: 2008-02-14 11:40:20

Метки материала: PostGIS, GIS, PostgreSQL, ГИС, БД, базы данных, производительность

Оставьте, пожалуйста, свой комментарий к публикации

Представиться как     Антибот:
   

Просьба не постить мусор. Если вы хотите потестить xBB, воспользуйтесь кнопкой предварительного просмотра на панели инструментов xBBEditor-а.


© 2007-2017, Дмитрий Скоробогатов.
Разрешается воспроизводить, распространять и/или изменять материалы сайта
в соответствии с условиями GNU Free Documentation License,
версии 1.2 или любой более поздней версии, опубликованной FSF,
если только иное не указано в самих материалах.