Как выполнить запрос в postgresql

Запрос данных PostgreSQL с помощью SELECT

PostgreSQL – это открытая система управления базами данных (СУБД), основанная на языке запросов SQL, которая часто используется для хранения данных веб-сайтов и других приложений.

В данном руководстве рассматривается процесс создания запросов данных PostgreSQL.

Примечание: Предполагается, что СУБД PostgreSQL предварительно установлена на сервер. В руководстве используется система Ubuntu 12.04, однако инструкции подойдут и для более современных дистрибутивов Linux.

Вход в PostgreSQL

Для работы можно загрузить тестовую БД.

Откройте сессию стандартного пользователя PostgreSQL:

Чтобы получить файл тестовой БД, введите:

Распакуйте архив и перейдите в полученный каталог:

tar xzvf world-1.0.tar.gz
cd dbsamples-0.1/world

Войдите в новое окружение:

Просмотр данных в PostgreSQL

Для начала нужно ознакомиться с тестовой БД. Чтобы просмотреть список таблиц, введите:

\d+
List of relations
Schema | Name | Type | Owner | Size | Description
——-+——————+——-+———-+———+————-
public | city | table | postgres | 264 kB |
public | country | table | postgres | 48 kB |
public | countrylanguage | table | postgres | 56 kB |
(3 rows)

Эта БД содержит три таблицы. Чтобы просмотреть столбцы одной из таблиц (например, city), выполните команду:

\d city
Table «public.city»
Column | Type | Modifiers
————+—————+————
id | integer | not null
name | text | not null
countrycode | character(3) | not null
district | text | not null
population | integer | not null
Indexes:
«city_pkey» PRIMARY KEY, btree (id)
Referenced by:
TABLE «country» CONSTRAINT «country_capital_fkey» FOREIGN KEY (capital) REFERENCES city(id)

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

Запрос данных при помощи select

Для запроса информации в PostgreSQL используется оператор select. Базовый синтаксис выглядит так:

SELECT columns_to_return FROM table_name;

К примеру, если запросить столбцы таблицы country (команда \d country), на экране появится объёмный вывод. Его можно отфильтровать, запросив только необходимые вам данные. Для примера запросите название страны и континент, на котором она находится.

Чтобы просмотреть все столбцы конкретной таблицы, используйте символ звёздочки (*).

Определение порядка записей

PostgreSQL позволяет определить порядок вывода запрошенных данных; для этого используется конструкция ORDER BY, идущая после оператора SELECT. Базовый синтаксис выглядит так:

SELECT columns FROM table ORDER BY column_names [ ASC | DESC ];

К примеру, чтобы запросить название стран и континент из таблицы country, а потом упорядочить вывод по континентам, нужно ввести следующую команду:

По умолчанию конструкция ORDER BY позволяет упорядочить вывод данных в порядке возрастания.

Чтобы изменить порядок вывода, нужно добавить в конструкцию ORDER BY оператор DESC. Например:

К примеру, можно отсортировать вывод по континенту, а затем по названию страны, и в результате получить алфавитный список стран для каждого континента:

Фильтрация записей PostgreSQL

PostgreSQL предоставляет механизмы для более детальной фильтрации вывода.

Один из таких механизмов – предложение where, которое позволяет задавать условия поиска.

К примеру, можно запросить все города Соединённых Штатов, добавив в конец команды конструкцию WHERE countrycode = ‘USA’.

Примечание: Условия поиска для предложения WHERE нужно помещать в одинарные кавычки.

Реляционный оператор like делает поиск ещё более гибким. Этот оператор использует шаблоны:

Для примера попробуйте запросить названия городов США, которые начинаются на N.

SELECT name FROM city WHERE countrycode = ‘USA’ AND name LIKE ‘N%’;
name
———————
New York
Nashville-Davidson
New Orleans
Newark
Norfolk
Newport News
Naperville
New Haven
North Las Vegas
Norwalk
New Bedford
Norman
(12 rows)

Этот вывод также можно получить в алфавитном порядке:

SELECT name FROM city WHERE countrycode = ‘USA’ AND name LIKE ‘N%’ ORDER BY name;
name
———————
Naperville
Nashville-Davidson
Newark
New Bedford
New Haven
New Orleans
Newport News
New York
Norfolk
Norman
North Las Vegas
Norwalk
(12 rows)

Продвинутое использование select

Теперь попробуйте создать более сложный запрос. В качестве примера можно рассмотреть следующий запрос:

Этот запрос состоит из нескольких частей.

В конце этого запроса находится знакомый оператор ORDER BY continent,country, который сортирует вывод по континенту, а затем по названию страны.

Чтобы понять остальные компоненты запроса, нужно ознакомиться с объединением таблиц.

Соединенные таблицы

Предложение JOIN позволяет запрашивать данные из нескольких связанных таблиц одновременно.

Примечание: Таблицы считаются связанными, если они имеют столбец, который относится к одним и тем же данным.

К примеру, в тестовой БД таблицы country и city связаны, поскольку совместно используют некоторые данные. Чтобы увидеть, что таблица country ссылается на таблицу city, введите:

Этот вывод говорит о том, что столбец capital таблицы country ссылается на столбец id таблицы city. По сути, можно воспринимать эти две таблицы как одну большую таблицу.

В предложенном выше запросе есть часть:

FROM country JOIN city ON country.capital = city.id

Это выражение значит, что Postgres вернёт данные из обеих таблиц. Предложение JOIN здесь является стандартным оператором соединения (также известен как inner join).

INNER JOIN выводит данные, присутствующие в обеих таблицах. Например, если соединить таблицы, которые не относятся друг к другу как внешние ключи, программа вернёт ошибку, поскольку таблицы содержат не связанные между собой данные. Для такого вывода стандартное предложение join не подходит.

Часть команды после ключевого слова on указывает общий столбец таблиц в таком формате:

В данном случае запрашиваются записи с общими значениями в обеих таблицах.

Заключение

Теперь у вас есть базовые навыки создания запросов, которые пригодятся при разработке или использовании приложений и интерактивных веб-страниц.

Источник

Курс молодого бойца PostgreSQL

Хочу поделиться полезными приемами работы с PostgreSQL (другие СУБД имеют схожий функционал, но могут иметь иной синтаксис).

Постараюсь охватить множество тем и приемов, которые помогут при работе с данными, стараясь не углубляться в подробное описание того или иного функционала. Я любил подобные статьи, когда обучался самостоятельно. Пришло время отдать должное бесплатному интернет самообразованию и написать собственную статью.

Данный материал будет полезен тем, кто полностью освоил базовые навыки SQL и желает учиться дальше. Советую выполнять и экспериментировать с примерами в pgAdmin‘e, я сделал все SQL-запросы выполнимыми без разворачивания каких-либо дампов.

1. Использование временных таблиц

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

Такие таблицы создаются как обычные, но с ключевым словом TEMP, и автоматически удаляются после завершения сессии.

Ключ ON COMMIT DROP автоматически удаляет таблицу (и все связанные с ней объекты) при завершении транзакции.

2. Часто используемый сокращенный синтаксис Postgres

можно записать менее громоздко:

* (две тильды со звездочкой)

Поиск регулярными выражениями (имеет отличный от LIKE синтаксис)
оператор

* (одна тильда и звездочка) регистронезависимая версия

Приведу пример поиска разными способами строк, которые содержат слово text

‘%text%’

* ‘%text%’

3. Общие табличные выражения (CTE). Конструкция WITH

Очень удобная конструкция, позволяет поместить результат запроса во временную таблицу и тут же использовать ее.

Примеры будут примитивны, чтобы уловить суть.

Таким способом можно ‘оборачивать’ какие-либо запросы (даже UPDATE, DELETE и INSERT, об этом будет ниже) и использовать их результаты в дальнейшем.

b) Можно создать несколько таблиц, перечисляя их нижеописанным способом

c) Можно даже вложить вышеуказанную конструкцию в еще один (и более) WITH

По производительности следует сказать, что не стоит помещать в секцию WITH данные, которые будут в значительной степени фильтроваться последующими внешними условиями (за пределами скобок запроса), ибо оптимизатор не сможет построить эффективный запрос. Удобнее всего положить в CTE результаты, к которым требуется несколько раз обращаться.

4. Функция array_agg(MyColumn).

Значения в реляционной базе хранятся разрозненно (атрибуты по одному объекту могут быть представлены в нескольких строках). Для передачи данных какому-либо приложению часто возникает необходимость собрать данные в одну строку (ячейку) или массив.
В PostgreSQL для этого существует функция array_agg(), она позволяет собрать в массив данные всего столбца (если выборка из одного столбца).
При использовании GROUP BY в массив попадут данные какого-либо столбца относительно каждой группы.

Сразу опишу еще одну функцию и перейдем к примеру.
array_to_string(array[], ‘;’) позволяет преобразовать массив в строку: первым параметром указывается массив, вторым — удобный нам разделитель в одинарных кавычках (апострофах). В качестве разделителя можно использовать

Выдаст результат:

Выполним обратное действие. Разложим массив в строки при помощи функции UNNEST, заодно продемонстрирую конструкцию SELECT columns INTO table_name. Помещу это в спойлер, чтобы статья не сильно разбухала.

5. Ключевое слово RETURNIG *

указанное после запросов INSERT, UPDATE или DELETE позволяет увидеть строки, которых коснулась модификация (обычно сервер сообщает лишь количество модифицированных строк).
Удобно в связке с BEGIN посмотреть на что именно повлияет запрос, в случае неуверенности в результате или для передачи каких либо id на следующий шаг.

Можно использовать в связке с CTE, организую безумный пример.

Таким образом, выполнится удаление данных, и удаленные значения передадутся на следующий этап. Все зависит от вашей фантазии и целей. Перед применением сложных конструкций обязательно изучите документацию вашей версии СУБД! (при параллельном комбинировании INSERT, UPDATE или DELETE существуют тонкости)

6. Сохранение результата запроса в файл

У команды COPY много разных параметров и назначений, опишу самое простое применение для ознакомления.

7. Выполнение запроса на другой базе

Не так давно узнал, что можно адресовать запрос к другой базе, для этого есть функция dblink (все подробности в мануале)

Если возникает ошибка:

«ERROR: function dblink(unknown, unknown) does not exist»

необходимо выполнить установку расширения следующей командой:

8. Функция similarity

Функция определения схожести одного значения к другому.

Использовал для сопоставления текстовых данных, которые были похожи, но не равны друг другу (имелись опечатки). Сэкономил уйму времени и нервов, сведя к минимуму ручную привязку.
similarity(a, b) выдает дробное число от 0 до 1, чем ближе к 1, тем точнее совпадение.
Перейдем к примеру. С помощью WITH организуем временную таблицу с вымышленными данными (и специально исковерканными для демонстрации функции), и будем сравнивать каждую строку с нашим текстом. В примере ниже будем искать то, что больше похоже на ООО «РОМАШКА» (подставим во второй параметр функции).

Получим следующий результат:

Если возникает ошибка

«ERROR: function similarity(unknown, unknown) does not exist»

необходимо выполнить установку расширения следующей командой:

Сортируем по similarity DESC. Первыми результатами видим наиболее похожие строки (1— полное сходство).

Необязательно выводить значение similarity в SELECT, можно просто использовать его в условии WHERE similarity(c_name, ‘ООО «РОМАШКА»’) >0.7
и самим задавать устраивающий нас параметр.

P.S. Буду признателен, если подскажете какие еще есть способы сопоставления текстовых данных. Пробовал убирать регулярными выражениями все кроме букв/цифр, и сопоставлять по равенству, но такой вариант не срабатывает, если присутствуют опечатки.

9. Оконные функции OVER() (PARTITION BY __ ORDER BY __ )

Почти описав в своем черновике этот очень мощный инструмент, обнаружил (с грустью и радостью), что подобная качественная статья на эту тему уже существует. Не вижу смысла дублировать информацию, поэтому рекомендую обязательно ознакомиться с данной статьей (ссылка — habrahabr.ru/post/268983/, автору низкий поклон ) тем, кто еще не умеет пользоваться оконными функциями SQL.

10. Множественный шаблон для LIKE

Задача. Необходимо отфильтровать список пользователей, имена которых должны соответствовать определенным шаблонам.

Как всегда, представлю простейший пример:

Имеем запрос, который выполняет свою функцию, но становится громоздким при большом количестве фильтров.

Продемонстрирую, как сделать его более компактным:

Можно проделать интересные трюки, используя подобный подход.
Напишите в комментариях, если есть мысли, как еще можно переписать исходный запрос.

11. Несколько полезных функций

NULLIF(a,b)
Возникают ситуации, когда определенное значение нужно трактовать как NULL.
Например, строки нулевой длины ( » — пустые строки) или ноль(0).
Можно написать CASE, но лаконичнее использовать функцию NULLIF, которая имеет 2 параметра, при равенстве которых возвращается NULL, иначе выводит исходное значение.

COALESCE выбирает первое не NULL значение

GREATEST выбирает наибольшее значение из перечисленных

LEAST выбирает наименьшее значение из перечисленных

PG_TYPEOF показывает тип данных столбца

PG_CANCEL_BACKEND останавливаем нежелательные процессы в базе

12. Экранирование символов

Начну с основ.
В SQL строковые значения обрамляются ‘ апострофом (одинарной кавычкой).
Числовые значения можно не обрамлять апострофами, а для разделения дробной части нужно использовать точку, т.к. запятая будет воспринята как разделитель

результат:

Все хорошо, до тех пор пока не требуется выводить сам знак апострофа ‘
Для этого существуют два способа экранирования (известных мне)

результат одинаковый:

В PostgreSQL существуют более удобный способ использовать данные, без экранирования символов. В обрамленной двумя знаками доллара $$ строке можно использовать практически любые символы.

получаю данные в первозданном виде:

Если этого мало, и внутри требуется использовать два символа доллара подряд $$, то Postgres позволяет задать свой «ограничитель». Стоит лишь между двумя долларами написать свой текст, например:

Увидим наш текст:

Для себя этот способ открыл не так давно, когда начал изучать написание функций.

Заключение

Надеюсь, данный материал поможет узнать много нового начинающим и «средничкам». Сам я не являюсь разработчиком, а могу лишь назвать себя любителем SQL, поэтому то, как использовать описанные приемы — решать Вам.

Желаю успехов в изучении SQL. Жду комментариев и благодарю за прочтение!
UPD. Вышло продолжение

Источник

Запросы в PostgreSQL: 1. Этапы выполнения

Привет, Хабр! Начинаю еще один цикл статей об устройстве PostgreSQL, на этот раз о том, как планируются и выполняются запросы.

В этом цикле я собираюсь рассмотреть:

этапы выполнения запросов (эта статья);

Материал перекликается с нашим учебным курсом QPT «Оптимизация запросов», но ограничивается только подробностями внутреннего устройства и не затрагивает оптимизацию как таковую. Кроме того, я ориентируюсь на еще не вышедшую версию PostgreSQL 14. А курс мы тоже скоро обновим (правда, на версию 13; приходится бежать со всех ног, чтобы только оставаться на месте).

Протокол простых запросов

Клиент-серверный протокол PostgreSQL позволяет в простом случае выполнять запросы SQL, отправляя их текст и получая в ответ сразу весь результат выполнения. Запрос, поступающий серверу на выполнение, проходит несколько этапов.

Разбор

Во-первых, текст запроса необходимо разобрать (parse), чтобы понять, что именно требуется выполнить.

Лексический и синтаксический разбор. Лексический анализатор разбирает текст запроса на лексемы (такие как ключевые слова, строковые и числовые литералы и т. п.), а синтаксический анализатор убеждается, что полученный набор лексем соответствует грамматике языка. PostgreSQL использует для разбора стандартные инструменты — утилиты Flex и Bison.

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

Возьмем для примера следующий запрос:

Для него в памяти обслуживающего процесса будет построено дерево, показанное на рисунке в сильно упрощенном виде. Рядом с узлами дерева подписаны части запроса, которые им соответствуют:

RTE — неочевидное сокращение от Range Table Entry. Именем range table в исходном коде PostgreSQL называются таблицы, подзапросы, результаты соединений — иными словами, наборы строк, над которыми работают операторы SQL.

Семантический разбор. Задача семантического анализа — определить, есть ли в базе данных таблицы и другие объекты, на которые запрос ссылается по имени, и есть ли у пользователя право обращаться к этим объектам. Вся необходимая для семантического анализа информация хранится в системном каталоге.

Семантический анализатор получает от синтаксического анализатора дерево разбора и перестраивает его, дополняя ссылками на конкретные объекты базы данных, информацией о типах данных и т. п.

Полное дерево можно получить в журнале сообщений сервера, установив параметр debug_print_parse, хотя практического смысла в этом немного.

Трансформация

Далее запрос может трансформироваться (переписываться).

Трансформации используются ядром для нескольких целей. Одна из них — заменять в дереве разбора имя представления на поддерево, соответствующее запросу этого представления.

В примере выше pg_tables — представление, и после трансформации дерево разбора примет следующий вид:

Это дерево разбора соответствует такому запросу (хотя все манипуляции производятся только над деревом, а не над текстом):

Дерево разбора отражает синтаксическую структуру запроса, но ничего не говорит о том, в каком порядке будут выполнены операции.

Разграничение доступа на уровне строк (row-level security) реализовано на этапе трансформации.

Еще один пример использования трансформаций ядром системы — реализация предложений SEARCH и CYCLE для рекурсивных запросов в версии 14.

PostgreSQL дает пользователю возможность написать свои собственные трансформации. Для этого используется система правил перезаписи (rules).

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

Полное дерево разбора после трансформации можно получить в журнале сообщений сервера, установив параметр debug_print_rewritten.

Планирование

SQL — декларативный язык: запрос определяет, какие данные надо получить, но не говорит, как именно их получать.

Любой запрос можно выполнить разными способами. Для каждой операции, представленной в дереве разбора, могут существовать разные способы ее выполнения: например, данные из таблицы можно получить, прочитав всю таблицу (и отбросив ненужное), а можно найти подходящие строки с помощью индекса. Наборы данных всегда соединяются попарно, что приводит к огромному количеству вариантов, отличающихся порядком соединений. Кроме того, существуют разные способы выполнить соединение двух наборов строк: например, можно перебирать строки одного набора и находить для них соответствие во втором наборе, а можно предварительно отсортировать оба набора и затем слить их вместе. Разные способы показывают себя лучше в одних ситуациях и хуже — в других.

Разница во времени выполнения между неоптимальным и оптимальным планами может составлять многие и многие порядки, поэтому планировщик, выполняющий оптимизацию разобранного запроса, является одним из самых сложных компонентов системы.

Дерево плана. План выполнения также представляется в виде дерева, но его узлы содержат не логические, а физические операции над данными.

Для исследовательских целей полное дерево плана можно получить в журнале сообщений сервера, установив параметр debug_print_plan. А на практике текстовое представление плана выводит команда EXPLAIN:

На рисунке выделены основные узлы дерева. В выводе команды EXPLAIN они отмечены стрелочками.

Узел Seq Scan в плане запроса соответствует чтению таблиц, а узел Nested Loop — соединению. Пока стоит обратить внимание на два момента:

из трех таблиц запроса в дереве осталось только две: планировщик понял, что одна из таблиц не нужна для получения результата и ее можно удалить из дерева плана;

каждый узел дерева снабжен информацией о предполагаемом числе обрабатываемых строк (rows) и о стоимости (cost).

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

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

Точное решение задачи оптимизации не гарантирует, что найденный план действительно будет лучшим, поскольку планировщик использует упрощенные математические модели и может действовать на основе не вполне точной входной информации.

Управление порядком соединений. Автор запроса может в известной степени сократить количество вариантов для перебора (взяв на себя ответственность за возможность упустить оптимальный план).

Общие табличные выражения обычно оптимизируются отдельно от основного запроса; в версии 12 такое поведение гарантирует предложение MATERIALIZE.

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

Значение параметра join_collapse_limit в сочетании с явными предложениями JOIN, а также значение параметра from_collapse_limit в сочетании с подзапросами могут зафиксировать порядок некоторых соединений в соответствии с синтаксической структурой запроса.

Последний пункт нуждается в пояснении. Рассмотрим запрос, в котором таблицы перечислены через запятую в предложении FROM без явного указания JOIN:

Ему соответствует следующий (схематично показанный) фрагмент дерева разбора:

Для такого запроса планировщик будет рассматривать все возможные порядки соединения.

В другом примере соединения имеют определенную структуру, определяемую предложением JOIN:

Дерево разбора отражает эту структуру:

Обычно планировщик уплощает дерево соединений, преобразуя его к тому же виду, что в первом примере. Алгоритм рекурсивно обходит дерево, заменяя узлы JOINEXPR плоским списком низлежащих элементов.

Но уплощение выполняется только если оно не приведет к появлению в плоском списке более join_collapse_limit элементов (значение параметра по умолчанию — 8). В данном примере при любых значениях параметра, меньших 5, узел JOINEXPR не будет уплощен. Для планировщика это означает, что

таблица B должна быть соединена с таблицей C (или наоборот, C с B — ограничение не накладывается на порядок соединения в паре),

таблицы A, D, E и результат соединения B с C могут быть соединены в любом порядке.

При значении параметра join_collapse_limit, равном 1, порядок любых явных соединений JOIN будет сохранен.

Кроме того, операция FULL OUTER JOIN никогда не уплощается независимо от значения параметра join_collapse_limit.

Точно так же и параметр from_collapse_limit (с тем же значеним 8 по умолчанию) ограничивает уплощение подзапросов. Хотя внешне подзапросы не похожи на соединения JOIN, но на уровне дерева разбора аналогия становится очевидной.

Вот пример запроса:

Ему соответствует дерево соединений:

По сути, разница только в том, что вместо узла JOINEXPR стоит узел FROMEXPR (отсюда и не совсем очевидное название параметра).

Генетический алгоритм. Если в дереве соединений (после процедуры уплощения) на одном уровне оказывается слишком много элементов — таблиц или результатов соединений, которые оптимизируются отдельно, — планирование может занять слишком много времени. При включенном (по умолчанию) параметре geqo PostgreSQL переключается на использование генетического алоритма1 при числе элементов, большем или равном значению параметра geqo_threshold (значение по умолчанию — 12).

Генетический алгоритм работает существенно быстрее алгоритма динамического программирования, но не гарантирует нахождение оптимального плана. Алгоритм имеет ряд настроек, которые я не буду рассматривать.

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

PostgreSQL решает эту задачу, вычисляя две компоненты стоимости. В плане запроса два эти числа следуют за словом cost, как в этой строке:

Первая компонента (начальная стоимость, startup cost) представляет затраты, которые требуется понести для подготовки к началу выполнения узла, а вторая (полная стоимость, total cost) — полные затраты на выполнение узла.

Чтобы решить, каким планам отдавать предпочтение, планировщик смотрит, используется ли курсор (команда SQL DECLARE или явное объявление курсора в PL/pgSQL). Если нет, то предполагается немедленное получение всех результирующих строк клиентом, и из просмотренных планов оптимизатор выбирает план с наименьшей полной стоимостью.

Для запроса, который выполняется с помощью курсора, выбирается план, оптимизирующий получение не всех строк, а только доли, равной значению параметра cursor_tuple_fraction (значение по умолчанию — 0.1). Говоря точнее, выбирается план с наименьшим значением выражения

startup cost + cursor_tuple_fraction (total cost − startup cost).

Общая схема вычисления оценки. Чтобы получить общую оценку плана, необходимо оценить каждый из его узлов. Стоимость узла зависит от типа этого узла (очевидно, что стоимость чтения данных из таблицы и стоимость сортировки отличаются) и от объема обрабатываемых этим узлом данных (обычно чем меньше, тем дешевле). Тип узла известен, а для оценки объема данных необходимо оценить кардинальность (количество строк) входных наборов данных и селективность (долю строк, которая останется на выходе) данного узла. А для этого надо иметь статистическую информацию о данных: размер таблиц, распределение данных по столбцам.

Таким образом, оптимизация зависит от корректной статистики, собираемой и обновляемой процессом автоанализа.

Если в каждом узле плана кардинальность оценена правильно, то и стоимость обычно адекватно отражает реальные затраты. Основные ошибки планировщика связаны именно с неправильной оценкой кардинальностей и селективностей. Это может происходить из-за некорректной или устаревшей статистики, невозможности ее использования или — в меньшей степени — из-за несовершенства моделей, лежащих в основе планировщика.

Оценка кардинальности. Оценка кардинальности — рекурсивный процесс. Чтобы оценить кардинальность узла плана, надо:

оценить кардинальности дочерних узлов и получить количество строк, поступающих узлу на вход;

оценить селективность самого узла, то есть долю входящих строк, которая останется на выходе.

Произведение одного на другое и даст кардинальность узла.

Селективность представляется числом от 0 до 1. Селективность, близкая к нулю, называется высокой, а близкая к единице — низкой. Это может показаться нелогичным, но селективность здесь понимается как избирательность: условие, выбирающее малую долю строк, обладает высокой селективностью (избирательностью), а условие, оставляющее почти все строки — низкой.

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

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

К сожалению, эти формулы предполагают независимость предикатов x и y. В случае коррелированных предикатов такая оценка будет неточной.

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

Аналогично оценивается кардинальность и других узлов, таких как сортировка или агрегация.

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

Оценка стоимости. Процесс оценки стоимости также рекурсивен. Чтобы рассчитать стоимость поддерева плана, надо рассчитать стоимости дочерних узлов, сложить их, и добавить стоимость самого узла.

Стоимость работы узла рассчитывается на основе математической модели операции, которую выполняет данный узел. Входными данными для оценки служит кардинальность, которая уже рассчитана. Отдельно оцениваются начальная и полная стоимость.

Некоторые операции не требуют никакой подготовки и начинают выполняться немедленно; у таких узлов начальная стоимость будет равна нулю.

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

Стоимость отражает оценку планировщика и, если планировщик ошибается, может не коррелировать с реальным временем выполнения. Стоимость нужна лишь для того, чтобы планировщик мог сравнивать разные планы одного и того же запроса в одних и тех же условиях. В остальных случаях сравнивать запросы (тем более разные) по стоимости — неправильно и бессмысленно. Например, стоимость могла быть недооценена из-за неправильной статистики; после актуализации статистики стоимость может вырасти, но стать более адекватной, и план на самом деле улучшится.

Выполнение

Оптимизированный запрос выполняется в соответствии с планом.

В памяти обслуживающего процесса создается портал — объект, хранящий состояние выполняющегося запроса. Состояние представляется в виде дерева, повторяющего структуру дерева плана.

Фактически узлы дерева работают как конвейер, запрашивая и передавая друг другу строки.

Выполнение начинается с корня. Корневой узел (в примере это операция сортировки SORT) обращается за данными к дочернему узлу. Получив все строки, узел выполняет сортировку и отдает данные выше, то есть клиенту.

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

Два листовых узла дерева SEQSCAN представляют обращение к таблицам. Когда вышестоящий узел обращается за данными, листовой узел читает очередную строку из соответствующей таблицы и возвращает ее.

Таким образом, часть узлов не хранит строки, а немедленно передает их выше и тут же забывает, но некоторым узлам (например, сортировке) требуется сохранять потенциально большой объем данных. Для этого в памяти обслуживающего процесса выделяется фрагмент размером work_mem (значение по умолчанию очень консервативно — 4MB); если этой памяти не хватает, данные сбрасываются на диск во временный файл.

В одном плане может быть несколько узлов, которым необходимо хранилище данных, поэтому для запроса может быть выделено несколько фрагментов памяти, каждый размером work_mem. Общий объем оперативной памяти, который может задействовать запрос, никак не ограничен.

Протокол расширенных запросов

При использовании протокола простых запросов каждая команда, даже если она повторяется из раза в раз, проходит все перечисленные выше этапы:

Но нет никакого смысла разбирать один и тот же запрос заново. Нет смысла разбирать и запросы, отличающиеся только константами: структура дерева разбора не изменится.

Еще одно неудобство простого способа выполнения запросов состоит в том, что клиент получает всю выборку сразу, сколько бы строк она не содержала.

В принципе, оба ограничения можно преодолеть, используя команды SQL: первое — подготавливая запрос командой PREPARE и выполняя с помощью EXECUTE, второе – создавая курсор командой DECLARE с последующей выборкой с помощью FETCH. Но для клиента это означает заботу об именовании создаваемых объектов, а для сервера — лишнюю работу по разбору дополнительных команд.

Поэтому расширенный клиент-серверный протокол позволяет детально управлять отдельными этапами выполнения операторов на уровне команд самого протокола.

Подготовка

На этапе подготовки запрос разбирается и трансформируется обычным образом, но полученное дерево разбора сохраняется в памяти обслуживающего процесса.

В PostgreSQL отсутствует глобальный кеш разобранных запросов. Поэтому даже если запрос уже был разобран одним обслуживающим процессом, другой будет вынужден разобрать его заново. Но у такого решения есть и плюсы. Под нагрузкой кеш в общей памяти легко становится узким местом из-за необходимости блокировок, и один клиент, выполняющий множество мелких команд, может повлиять на производительность всего экземпляра. В PostgreSQL разбор запросов обходится дешево и не влияет на другие процессы.

При подготовке запроса его можно параметризовать. Вот простой пример на уровне SQL-команд (повторюсь, что это не совсем то же самое, что подготовка на уровне команд протокола, но в конечном счете эффект тот же):

Посмотреть именованные подготовленные операторы можно в представлении:

Увидеть таким образом безымянные операторы (которые использует расширенный протокол или PL/pgSQL) не получится. И, конечно, сеанс может увидеть только собственные подготовленные операторы; заглянуть в память другого сеанса невозможно.

Привязка параметров

Перед выполнением подготовленного запроса выполняется привязка фактических значений параметров.

Преимущество параметров подготовленных операторов перед конкатенацией литералов со строкой запроса — принципиальная невозможность внедрения SQL-кода, поскольку любое значение параметра не сможет изменить уже построенное дерево разбора. Чтобы достичь того же уровня безопасности без подготовленных операторов, требуется аккуратно экранировать каждое значение, полученное из ненадежного источника.

Планирование и выполнение

Когда дело доходит до выполнения подготовленного оператора, происходит планирование с учетом значений фактических параметров, после чего план передается на выполнение.

Учитывать значения параметров важно, поскольку оптимальные планы для разных значений могут не совпадать. Например, поиск очень дорогих бронирований использует индекс (о чем говорят слова Index Scan), поскольку планировщик предполагает, что подходящих строк не очень много (rows):

Однако под следующее условие попадают вообще все бронирования, поэтому индекс бесполезен и таблица просматривается целиком (Seq Scan):

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

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

Подготовленный оператор plane уже был один раз выполнен. После еще двух выполнений по-прежнему используются частные планы, что видно по значению параметра в плане запроса:

После еще одного, четвертого, выполнения планировщик переключится на использование общего плана — он совпадает с частными планами, имеет ту же стоимость, и поэтому предпочтителен. Команда EXPLAIN показывает теперь не значение параметра, а его номер:

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

Поэтому (начиная с версии 12) при неправильном автоматическом решении можно принудиительно выбрать общий либо частный план, установив значение параметра plan_cache_mode:

Начиная с версии 14 представление pg_prepared_statements показывает в том числе и статистику выборов планов:

Получение результатов

Протокол расширенных запросов позволяет клиенту получать не все результирующие строки сразу, а выбирать данные по несколько строк за раз. Почти тот же эффект дает использование SQL-курсоров (за исключением лишней работы для сервера и того факта, что планировщик оптимизирует получение не всей выборки, а первых cursor_tuple_fraction строк):

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

Источник