Ниже черновик так и не дописанной в свое время статьи, это просто заметки на полях, на полноту и полную объективность не претендует.
Заметки по оптимизации PostgreSQL базы
--------------------------
1. Диагностика узких мест.
Для определения узких мест очень помогает создание промежуточной библиотеки,
в которой реализован отладочный режим, при активации которого в лог файл
пишется каждый запрос и время его выполнения. Можно обойтись просто определив
время до запроса и после, а затем произвести запись разницы и тела запроса в лог.
Далее, имея перед глазами текущую структуру базы и список индексов, используем
оператор "EXPLAIN [ ANALYZE ] [ VERBOSE ] запрос" для определения причины
задержек при выполнении самых медленных запросов.
Например:
EXPLAIN SELECT * FROM news ORDER BY enter_date LIMIT 100;
Limit (cost=531.60..531.85 rows=100 width=572)
-> Sort (cost=531.60..536.17 rows=1827 width=572)
Sort Key: enter_date
-> Seq Scan on news (cost=0.00..164.27 rows=1827 width=572)
EXPLAIN SELECT * FROM news ORDER BY enter_date;
Sort (cost=531.60..536.17 rows=1827 width=572)
Sort Key: enter_date
-> Seq Scan on news (cost=0.00..164.27 rows=1827 width=572)
CREATE INDEX idx_news_date ON news (enter_date);
VACUUM ANALYZE;
EXPLAIN SELECT * FROM news ORDER BY enter_date;
Sort (cost=531.60..536.17 rows=1827 width=572)
Sort Key: enter_date
-> Seq Scan on news (cost=0.00..164.27 rows=1827 width=572)
EXPLAIN SELECT * FROM news ORDER BY enter_date LIMIT 100;
Limit (cost=0.00..32.66 rows=100 width=572)
-> Index Scan using idx_news_date on news (cost=0.00..596.68 rows=1827 width=572)
и т.д.
--------------------------
2. Профилактика и оптимизация часто обновляемых таблиц.
Есть простое правило: чем чаще обновляется таблица, тем чаще нужно делать
"VACUUM ANALYZE" (обновление статистики для оптимизатора).
Если UPDATE преобладает над INSERT, то полезно применять "VACUUM FREEZE"
который имеет один большой недостаток, во время его работы таблица
блокируется, проведение каких-либо операций с ней невозможно (VACUUM ANALYZE
для PostgreSQL 7.2.x и старше не требует блокировки). Даже в простейшей
таблице содержащей строковые поля, элементы которой обновляются, допустим,
раз в 5 минут (подсчет трафика на интерфейсах по SNMP), за неделю размер файла
базы вырастает на несколько порядков, а с ним замедляется и скорость выполнения запросов,
особенно по непроиндексированным полям.
При частом обновлении (INSERT или UPDATE) использование индексов может обернуться
во вред, ведь при обновлении SQL сервер обновляет не только данные в таблице,
но и данные в индексе.
Если одновременно обновляется большое число записей, то увеличить
скорость обновления можно поместив апдейт в BEGIN WORK/COMMIT или вообще
использовать эксклюзивный лок таблицы "LOCK TABLE EXCLUSIVE".
--------------------------
3. Индексы или советы по оптимизации таблиц с высокой частотой выборки данных.
При использовании индексов для их хранения требуется дополнительное дисковое
пространство, если проиндексированы текстовые поля, то это пространство может
быть сравнимо с размером самой таблицы. Так же оптимизатор при обилии индексов,
(особенно по нескольким полям или пересекающихся) может "запутаться" и оптимизировать
выполнение запроса не в лучшую сторону.
- Индекс для полей используемых в "JOIN", "ORDER BY" и "GROUP BY", "MAX()" и
"MIN()" не менее важен, чем индекс для полей в "WHERE" условиях (в PostgreSQL
для "ORDER BY", MIN(), MAX() часто используется "Seq Scan" вместо индекса,
каждый случай нужно рассматривать используя EXPLAIN, иногда использование LIMIT помогает "выбрать" index вместо Seq Scan).
- Лучше не индексировать поля имеющие строковый тип, особенно поля типа text.
- Индекс для LIKE и ~ (regex) полезен только когда маска не идет вначале, т.е. при
указании '%text' индекс не будет использован, а при 'text% будет (для regex
индекс используется только для масок вида '^text...').
- Если в запросе используются функции LOWER/UPPER или ILIKE, то индекс будет
использован только если он был построен с учетом регистра, т.е. например
"CREATE INDEX news_ilike ON news (lower(title));"
- Не следует индексировать boolean поля или числовые поля имеющие небольшой
разброс значений (флаговые поля), в данном случае индекс больше навредит, чем
окажет пользу.
- Для полей отражающих дату/время и числовых больше подходит btree индекс,
для текстовых - hash, для индексов по двум и более полям возможно использовать только tree. btree индексы лучше подходят для операций '<','>', сортировки, а hash для '=' и '<>'.
Если не уверен какой тип индекса использовать лучше использовать btree. Примеры:
CREATE UNIQUE INDEX "idx_news1" on "news" ("news_id");
CREATE INDEX "idx_news2" on "news" using btree ("news_time");
CREATE UNIQUE INDEX "idx_profile" on "profile" using hash ("login");
- При создании таблицы с UNIQUE и PRIMARY KEY индексы для этих полей создаются
автоматически.
- Оптимизатору сильно помогает VACUUM ANALYZE при принятии решения использовать
ли индексы;
- На небольших таблицах, прямой перебор бывает быстрее использования индекса,
из-за лишних дисковых операций (на небольших таблицих оптимизатор может отказаться
от использоватния индексов автоматически).
- UNIQUE индексы быстрее, чем индексы не по уникальным полям.
- Поля text в которых заведомо будет храниться большой объем данных (например, текст
статей) лучше отделить от остальных атрибутов, таких как время, автор, раздел...
Т.е. две таблицы вместо одной:
---tab1----
ключ PRIMARY KEY
заголовок
время
автор
раздел
флаг подтверждения
--------
---tab2----
ключ UNIQUE REFERENCES tab1(ключ)
текст статьи
--------
Индексы по tab1.ключ, tab2.ключ (при UNIQUE и PRIMARY KEY будут созданы автоматически),
tab1.время.
- Поля помеченные как NOT NULL немного экономят место и исключают лишние проверки.
- Вместо типа text лучше использовать character varying(N).
- Чем меньше размер типа, тем лучше. Т.е. где лучше использовать int4 вместо int8,
идеально когда в таблице вообще нет строковых типов и особенно типа text.
Вариант для распечатки
Скрипты, настройки и файлы конфигурации. (Public)
on
14-Мрт-03, 11:40 (MSK)
