буква foo_name = буква, в любом регистре». Вы можете создать индекс
CREATE INDEX foo_name_first_idx ON foo ((lower(substr(foo_name, 1, 1))));
и запрос вида
SELECT * FROM foo WHERE lower(substr(foo_name, 1, 1)) = 'ы';
будет его использовать.
Частичные индексы (partial indexes)
Под частичным индексом понимается индекс с предикатом WHERE. Пусть, например, у вас есть в базе таблица scheta с параметром uplocheno типа boolean. Записей, где uplocheno = false меньше, чем записей с uplocheno = true, а запросы по ним выполняются значительно чаще. Вы можете создать индекс
CREATE INDEX scheta_neuplocheno ON scheta (id) WHERE NOT uplocheno;
который будет использоваться запросом вида
SELECT * FROM scheta WHERE NOT uplocheno AND ...;
Достоинство подхода в том, что записи, не удовлетворяющие условию WHERE, просто не попадут в индекс.
и поэтому EXPLAIN ANALYZE DELETE не слишком хорошая идея
Перенос логики на сторону сервера
Этот пункт очевиден для опытных пользователей PostrgeSQL и предназначен для тех, кто использует или переносит на PostgreSQL приложения, написанные изначально для более примитивных СУБД.
Реализация части логики на стороне сервера через хранимые процедуры, триггеры, правила1 часто позволяет ускорить работу приложения. Действительно, если несколько запросов объединены в процедуру, то не требуется
пересылка промежуточных запросов на сервер;
получение промежуточных результатов на клиент и их обработка.
Кроме того, хранимые процедуры упрощают процесс разработки и поддержки: изменения надо вносить только на стороне сервера, а не менять запросы во всех приложениях.
RULE реализованное в PostgreSQL расширение стандарта SQL, позволяющее, в частности, создавать обновляемые представления
Оптимизация конкретных запросов
В этом разделе описываются запросы, для которых по разным причинам нельзя заставить оптимизатор использовать индексы, и которые будут всегда вызывать полный просмотр таблицы. Таким образом, если вам требуется использовать эти запросы в требовательном к быстродействию приложении, то придётся их изменить.
SELECT count(*) FROM <огромная таблица>
Функция count() работает очень просто: сначала выбираются все записи, удовлетворяющие условию, а потом к полученному набору записей применяется агрегатная функция считается количество выбранных строк. Информация о видимости записи для текущей транзакции (а конкурентным транзакциям может быть видимо разное количество записей в таблице!) не хранится в индексе, поэтому, даже если использовать для выполнения запроса индекс первичного ключа таблицы, всё равно потребуется чтение записей собственно из файла таблицы.
Проблема Запрос вида
SELECT count(*) FROM foo;
осуществляет полный просмотр таблицы foo, что весьма долго для таблиц с большим количеством записей.
Решение Простого решения проблемы, к сожалению, нет. Возможны следующие подходы:
Если точное число записей не важно, а важен порядок1, то можно использовать информацию о количестве записей в таблице, собранную при выполнении команды ANALYZE:SELECT reltuples FROM pg_class WHERE relname = 'foo';
Если подобные выборки выполняются часто, а изменения в таблице достаточно редки, то можно завести вспомогательную таблицу, хранящую число записей в основной. На основную же таблицу повесить триггер, который будет уменьшать это число в случае удаления записи и увеличивать в случае вставки. Таким образом, для получения количества записей потребуется лишь выбрать одну запись из вспомогательной таблицы;
Вариант предыдущего подхода, но данные во вспомогательной таблице обновляются через определённые промежутки времени (cron).
Медленный DISTINCT
Текущая реализация DISTINCT для больших таблиц очень медленна. Но возможно использовать GROUP BY взамен DISTINCT. GROUP BY может использовать агрегирующий хэш, что значительно быстрее, чем DISTINCT (актуально до версии 8.4 и ниже).
postgres=# select count(*) from (select distinct i from g) a; count ------- 19125 (1 row) Time: 580,553 ms postgres=# select count(*) from (select distinct i from g) a; count ------- 19125 (1 row) Time: 36,281 ms
postgres=# select count(*) from (select i from g group by i) a; count -------
19125 (1 row) Time: 26,562 ms postgres=# select count(*) from (select i from g group by i) a; count ------- 19125 (1 row) Time: 25,270 ms
«на нашем форуме более 10000 зарегистрированных пользователей, оставивших более 50000 сообщений!»
Утилиты для оптимизации запросов
pgFouine
pgFouine это анализатор log-файлов для PostgreSQL, используемый для генерации детальных отчетов из log-файлов PostgreSQL. pgFouine поможет определить, какие запросы следует оптимизировать в первую очередь. pgFouine написан на языке программирования PHP с использованием объектно-ориентированных технологий и легко расширяется для поддержки специализированных отчетов, является свободным программным обеспечением и распространяется на условиях GNU General Public License. Утилита спроектирована таким образом, чтобы обработка очень больших log-файлов не требовала много ресурсов.
Для работы с pgFouine сначала нужно сконфигурировать PostgreSQL для создания нужного формата log-файлов: