Postgresql какие запросы выполняются

SQL-Ex blog

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

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

Проблемы, связанные с размером

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

SELECT pg_database.datname, pg_size_pretty(pg_database_size(pg_database.datname)) AS size FROM pg_database ORDER BY pg_database_size(pg_database.datname) desc;

SELECT 
nspname || '.' || relname AS "relation", pg_size_pretty(pg_relation_size(C.oid)) AS "size" 
FROM pg_class C 
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) 
WHERE nspname NOT IN ('pg_catalog', 'information_schema') 
ORDER BY pg_relation_size(C.oid) DESC 
LIMIT 20;

SELECT schemaname,relname,n_live_tup,n_dead_tup,last_vacuum,last_autovacuum FROM pg_stat_user_tables ORDER BY n_dead_tup DESC limit 10;

schemaname | relname | n_live_tup | n_dead_tup | last_vacuum | last_autovacuum 
public | campaign_session_denormalized_data | 1123219 | 114268349 | 2021–01–10 18:27:34.050087+05:30 | 2021–01–19 14:08:58.062574+05:30

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

Время от времени, благодаря MVCC, ваша таблица может увеличиться в размерах (называемое раздуванием таблицы) — именно поэтому необходим регулярный VACUUM. Этот запрос покажет вам список таблиц и индексов с наибольшим раздуванием. Значение представляет число «потраченных впустую байтов», или разницу между тем, что используется на текущий момент таблицей или индексом, и что должно быть по расчетам.

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

with foo as ( 
SELECT 
schemaname, tablename, hdr, ma, bs, 
SUM((1-null_frac)*avg_width) AS datawidth, 
MAX(null_frac) AS maxfracsum, 
hdr+( 
SELECT 1+COUNT(*)/8 
FROM pg_stats s2 
WHERE null_frac<>0 AND s2.schemaname = s.schemaname AND s2.tablename = s.tablename 
) AS nullhdr 
FROM pg_stats s, ( 
SELECT 
(SELECT current_setting('block_size')::NUMERIC) AS bs, 
CASE WHEN SUBSTRING(v,12,3) IN ('8.0','8.1','8.2') THEN 27 ELSE 23 END AS hdr, 
CASE WHEN v ~ 'mingw32' THEN 8 ELSE 4 END AS ma 
FROM (SELECT version() AS v) AS foo 
) AS constants 
GROUP BY 1,2,3,4,5 
), rs as ( 
SELECT 
ma,bs,schemaname,tablename, 
(datawidth+(hdr+ma-(CASE WHEN hdr%ma=0 THEN ma ELSE hdr%ma END)))::NUMERIC AS datahdr, 
(maxfracsum*(nullhdr+ma-(CASE WHEN nullhdr%ma=0 THEN ma ELSE nullhdr%ma END))) AS nullhdr2 
FROM foo 
), sml as ( 
SELECT 
schemaname, tablename, cc.reltuples, cc.relpages, bs, 
CEIL((cc.reltuples*((datahdr+ma- 
(CASE WHEN datahdr%ma=0 THEN ma ELSE datahdr%ma END))+nullhdr2+4))/(bs-20::FLOAT)) AS otta, 
COALESCE(c2.relname,'?') AS iname, COALESCE(c2.reltuples,0) AS ituples, COALESCE(c2.relpages,0) AS ipages, 
COALESCE(CEIL((c2.reltuples*(datahdr-12))/(bs-20::FLOAT)),0) AS iotta -- очень грубая аппроксимация, предполагаются все столбцы 
FROM rs 
JOIN pg_class cc ON cc.relname = rs.tablename 
JOIN pg_namespace nn ON cc.relnamespace = nn.oid AND nn.nspname = rs.schemaname AND nn.nspname <> 'information_schema' 
LEFT JOIN pg_index i ON indrelid = cc.oid 
LEFT JOIN pg_class c2 ON c2.oid = i.indexrelid 
) 
SELECT 
current_database(), schemaname, tablename, /*reltuples::bigint, relpages::bigint, otta,*/ 
ROUND((CASE WHEN otta=0 THEN 0.0 ELSE sml.relpages::FLOAT/otta END)::NUMERIC,1) AS tbloat, 
CASE WHEN relpages < otta THEN 0 ELSE bs*(sml.relpages-otta)::BIGINT END AS wastedbytes,
iname, /*ituples::bigint, ipages::bigint, iotta,*/ 
ROUND((CASE WHEN iotta=0 OR ipages=0 THEN 0.0 ELSE ipages::FLOAT/iotta END)::NUMERIC,1) AS ibloat, 
CASE WHEN ipages < iotta THEN 0 ELSE bs*(ipages-iotta) END AS wastedibytes
FROM sml 
ORDER BY wastedbytes DESC

Пример вывода

current_database | schemaname | tablename | tbloat | wastedbytes | iname | ibloat | wastedibytes 
dashboard | public | job_logs | 1.1 | 4139507712 | job_logs_pkey | 0.2 | 0 
dashboard | public | job_logs | 1.1 | 4139507712 | index_job_logs_on_job_id_and_created_at | 0.4 | 0 
dashboard | public | events | 1.1 | 3571736576 | events_pkey | 0.1 | 0 
dashboard | public | events | 1.1 | 3571736576 | index_events_on_tenant_id | 0.1 | 0 
dashboard | public | events | 1.1 | 3571736576 | index_events_on_event_type | 0.2 | 0 
dashboard | public | jobs | 1.1 | 2013282304 | index_jobs_on_status | 0.0 | 0 
dashboard | public | jobs | 1.1 | 2013282304 | index_jobs_on_tag | 0.3 | 0 
dashboard | public | jobs | 1.1 | 2013282304 | index_jobs_on_tenant_id | 0.2 | 0 
dashboard | public | jobs | 1.1 | 2013282304 | index_jobs_on_created_at | 0.2 | 0 
dashboard | public | jobs | 1.1 | 2013282304 | index_jobs_on_created_at_queued_or_running | 0.0 | 21086208

• tbloat: раздувание таблицы, отношение между текущим и оптимизированным значениями.
• wastedbytes: число растраченных байтов.
• ibloat & wastedibytes: то же самое, что и выше, только для индексов.

Проблемы, связанные с производительностью

Получить выполняющиеся запросы (и состояние блокировок) в PostgreSQL

SELECT S.pid, age(clock_timestamp(), query_start),usename,query,L.mode,L.locktype,L.granted,s.datname FROM pg_stat_activity S inner join pg_locks L on S.pid = L.pid order by L.granted, L.pid DESC;

SELECT pg_cancel_backend(pid);

SQL-Ex blog

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

Жизненный цикл запроса в базе данных PostgreSQL

• Зачем нам вообще нужен план запроса?
• Что точно представлено в плане?
• PostgreSQL недостаточно умен, чтобы оптимизировать мои запросы автоматически? Почему я должен беспокоиться о планировщике?
• Планировщик — это единственное, куда я должен смотреть?

Диаграмма жизненного цикла запроса в PostgreSQL

Первая фаза — это подключение к базе данных либо через ODBC/JDBC (API, созданный Microsoft и Oracle, соответственно, для взаимодействия с базами данных), либо другими средствами типа PSQL (интерфейс терминала для Postgres).

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

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

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

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

Установка данных

Давайте установим некую фиктивную таблицу с тестовыми данными для выполнения экспериментов.

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

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

Поскольку id есть serial, он будет автоматически заполняться самим PostgreSQL.

Теперь таблица содержит 1119284 записей.

Большинство последующих примеров будут основаны на этой таблице. Она намеренно сделана простой, чтобы сфокусироваться на процессе, а не на сложности таблицы/данных.

Переходим к этапу планирования

PostgreSQL и многие другие системы баз данных позволяют пользователям видеть то, что фактически происходит под капотом на этапе планирования. Мы можем сделать это, выполняя то, что называется командой EXPLAIN (объяснить).

ОБЪЯСНЕНИЕ запроса в PostgreSQL

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

Объяснение и анализ вместе

Добавление аргумента ANALYZE к запросам выводит информацию о времени.

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

Что такое буферы и кэши в базах данных?

Давайте перейдем к более интересной метрике, которая называется BUFFERS. Она объясняет как много данных приходит из кэша PostgreSQL и как много считывается с диска.

Включение BUFFERS в качестве аргумента показывает, сколько страниц попадает в запрос.

Buffers : shared hit=5 означает, что пять страниц было извлечено из кэша самого PostgreSQL. Давайте настроим смещение в запросе для получения отличных строк.

Изменение OFFSET приводит к отличающемуся числу запросов страниц.

Buffers: shared hit=7 read=5 показывает, что 5 страниц бралось с диска. Часть read — это переменная, которая показывает как много страниц читались с диска, а hit, как уже объяснялось, берутся из кэша. Если мы выполним тот же запрос опять (помним, что ANALYSE выполняет запрос), то теперь все данные будут находиться в кэше.

Повторное выполнение запроса показывает, что кэш теперь предоставляет все результаты.

PostgreSQL использует механизм кэша, называемый LRU (Least Recently Used — реже всего используемые за последнее время), для хранения в памяти часто используемых данных. Понимание того, как работает кэш, и его важность, это материал для другой статьи, но сейчас мы должны осознать, что PostgreSQL имеет надежный механизм кэширования, и мы можем увидеть его работу, используя команду EXPLAIN (ANALYSE, BUFFERS).

Аргумент команды VERBOSE

Verbose — еще один аргумент команды, который дает дополнительную информацию.

Аргумент команды VERBOSE даст еще больше информации для сложного запроса.

Заметим, что Output: id, name, sentence, company является дополнительным. В плане сложного запроса будет много другой информации, которая будет напечатана. По умолчанию опции COSTS и TIMING установлены в TRUE, и нет необходимости указывать их явно, если вы не захотите их отключить (FALSE).

FORMAT в объяснении PostgreSQL

PostgreSQL имеет возможность представлять план в замечательном формате, таком как JSON, поэтому эти планы могут интерпретироваться независимым от языка способом.

Этот запрос напечатает план запроса в формате JSON. Вы можете просмотреть этот формат в Arctype, скопировав его вывод и вставив его в другую таблицу, как показано ниже в GIF.

Вставка вывода EXPLAIN JSON в таблицу, и использование просмотра JSON для проверки.

• Text (по умолчанию)
• JSON (в примере выше)
• XML
• YAML

• EXPLAIN — тип плана, с которого вы обычно начинаете, и используемый, главным образом, в промышленных системах.
• EXPLAIN ANALYSE используется для выполнения запроса, наряду с получением плана запроса. Так вы получаете разбивку в плане времени планирования и времени выполнения, и сравнение по стоимости и фактическое время выполненного запроса.
• EXPLAIN (ANALYSE, BUFFERS) используется поверх анализа для получения числа строк, читаемых из кэша и диска, и того, как ведет себя кэш.
• EXPLAIN (ANALYSE, BUFFERS, VERBOSE) для получения подробной и дополнительной информации, относящейся к запросу.
• EXPLAIN(ANALYSE,BUFFERS,VERBOSE,FORMAT JSON) — какой формат вывода вы хотите получить; в данной случае JSON.

Элементы плана запроса

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

Узлы запроса

План запроса состоит из узлов:

Узлы — ключевая часть выполнения запроса

Узел можно представлять себе как этап выполнения запроса базой данных. Узлы зачастую являются вложенными, как показано выше; Seq Scan выполняется раньше, после чего применяется предложение Limit. Давайте добавим предложение Where, чтобы понять дальнейшее вложение.

Выполнения следует порядку изнутри наружу.

• Фильтрация строк по имени Sandra Smith.
• Выполнение последовательного сканирования при применении вышеприведенного фильтра.
• Применение предложения limit наверху.

Как можно видеть, база данных понимает, что необходимы только 10 строк, и сканирование не будет продолжено, как только будут получены требуемые 10 строк. Пожалуйста, отметьте, что я отключил SET max_parallel_workers_per_gather =0; поэтому этот план получился проще. Мы исследуем параллелизм в последующей статье.

Стоимость в планировщике запросов

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

Стоимость представлена внутри вывода EXPLAIN.

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

Планирование и выполнение базой данных

Время планирования и выполнения — это метрики, которые выводятся только с опцией EXPLAIN ANALYSE.

Планирование и выполнение — это две разные фазы выполнения запроса.

Планировщик (Planning Time — время планирования) решает, как запрос должен выполняться на основе различных параметров, а исполнитель (Execution Time) выполняет запрос. Эти указанные выше параметры являются абстрактными и применяются к любому типу запросов. Время выполнения представлено в миллисекундах. Во многих случаях время планирования и время выполнения могут различаться и, как в примере выше, планировщику может потребоваться больше времени для планирования запроса, а исполнителю требуется меньше времени, что обычно не так. Им не обязательно необходимо соответствовать друг другу, но если они сильно расходятся, тогда пора задуматься о том, почему это произошло.

В типичных OLTP системах, к которым относится и PostgreSQL, любая комбинация планирования и выполнения должна быть менее 50мс, если это не аналитический запрос/огромные записи/известные исключения. Напомним, что OLTP — это оперативная обработка транзакций. В типичном бизнесе выполняется от сотен до миллионов транзакций. Всегда следует очень внимательно следить за временем выполнения, поскольку запросы с невысокой стоимостью могут в сумме добавить высокие накладные расходы.

Куда двигаться дальше

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

Давайте вернемся к вопросам, которые мы задавали выше.

Q: Зачем нам вообще нужен план?
A: «Дурак с планом лучше, чем гений без плана!» — старая поговорка. План абсолютно необходим, чтобы решить, какой путь избрать, особенно когда решение принимается на основе статистики.

Q: Что точно находится в плане?
A: План состоит из узлов, стоимостей, времени планирования и времени выполнения. Узлы — это фундаментные блоки построения запроса. Стоимость — это основной атрибут узла. Время планирования и выполнения показывает фактическое время.

Q: Разве PostgreSQL недостаточно умен, чтобы оптимизировать мои запросы автоматически? Почему я должен беспокоиться о планировщике?
A: PostgreSQL действительно умен настолько, насколько это возможно. Планировщик становится лучше и лучше с каждым релизом, но не существует такого полностью автоматизированного/совершенного планировщика. В действительности это непрактично, т.к. оптимизация может быть хорошей для одного запроса, но плохой для другого. Планировщик должен где-то прочертить линию и обеспечить согласованное поведение и производительность. Большая ответственность лежит на разработчиках/администраторах баз данных, чтобы писать оптимизированные запросы и лучше понимать поведение базы данных.

Q: Достаточно ли мне смотреть только на планировщика?
A: Конечно, нет. Имеется много других вещей, таких как предметная экспертиза приложения, проектирование таблиц, архитектура базы данных и т.д., которые очень важны. Но вам, как разработчику/администратору баз данных, понимание и улучшение этих абстрактных навыков чрезвычайно важно для карьерного роста.

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

Postgresql какие запросы выполняются

WITH предоставляет способ записывать дополнительные операторы для применения в больших запросах. Эти операторы, которые также называют общими табличными выражениями (Common Table Expressions, CTE ), можно представить как определения временных таблиц, существующих только для одного запроса. Дополнительным оператором в предложении WITH может быть SELECT , INSERT , UPDATE или DELETE , а само предложение WITH присоединяется к основному оператору, которым также может быть SELECT , INSERT , UPDATE или DELETE .

7.8.1. SELECT в WITH

Основное предназначение SELECT в предложении WITH заключается в разбиении сложных запросов на простые части. Например, запрос:

WITH regional_sales AS ( SELECT region, SUM(amount) AS total_sales FROM orders GROUP BY region ), top_regions AS ( SELECT region FROM regional_sales WHERE total_sales > (SELECT SUM(total_sales)/10 FROM regional_sales) ) SELECT region, product, SUM(quantity) AS product_units, SUM(amount) AS product_sales FROM orders WHERE region IN (SELECT region FROM top_regions) GROUP BY region, product;

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

Необязательное указание RECURSIVE превращает WITH из просто удобной синтаксической конструкции в средство реализации того, что невозможно в стандартном SQL. Используя RECURSIVE , запрос WITH может обращаться к собственному результату. Очень простой пример, суммирующий числа от 1 до 100:

WITH RECURSIVE t(n) AS ( VALUES (1) UNION ALL SELECT n+1 FROM t WHERE n < 100 ) SELECT sum(n) FROM t;

В общем виде рекурсивный запрос WITH всегда записывается как не рекурсивная часть, потом UNION (или UNION ALL ), а затем рекурсивная часть, где только в рекурсивной части можно обратиться к результату запроса. Такой запрос выполняется следующим образом:

Вычисляется не рекурсивная часть. Для UNION (но не UNION ALL ) отбрасываются дублирующиеся строки. Все оставшиеся строки включаются в результат рекурсивного запроса и также помещаются во временную рабочую таблицу.

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

Примечание

Строго говоря, этот процесс является итерационным, а не рекурсивным, но комитетом по стандартам SQL был выбран термин RECURSIVE .

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

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

WITH RECURSIVE included_parts(sub_part, part, quantity) AS ( SELECT sub_part, part, quantity FROM parts WHERE part = 'our_product' UNION ALL SELECT p.sub_part, p.part, p.quantity FROM included_parts pr, parts p WHERE p.part = pr.sub_part ) SELECT sub_part, SUM(quantity) as total_quantity FROM included_parts GROUP BY sub_part

Работая с рекурсивными запросами, важно обеспечить, чтобы рекурсивная часть запроса в конце концов не выдала никаких кортежей (строк), в противном случае цикл будет бесконечным. Иногда для этого достаточно применять UNION вместо UNION ALL , так как при этом будут отбрасываться строки, которые уже есть в результате. Однако часто в цикле выдаются строки, не совпадающие полностью с предыдущими: в таких случаях может иметь смысл проверить одно или несколько полей, чтобы определить, не была ли текущая точка достигнута раньше. Стандартный способ решения подобных задач — вычислить массив с уже обработанными значениями. Например, рассмотрите следующий запрос, просматривающий таблицу graph по полю link :

WITH RECURSIVE search_graph(id, link, data, depth) AS ( SELECT g.id, g.link, g.data, 1 FROM graph g UNION ALL SELECT g.id, g.link, g.data, sg.depth + 1 FROM graph g, search_graph sg WHERE g.id = sg.link ) SELECT * FROM search_graph;

Этот запрос зациклится, если связи link содержат циклы. Так как нам нужно получать в результате « depth » , одно лишь изменение UNION ALL на UNION не позволит избежать зацикливания. Вместо этого мы должны как-то определить, что уже достигали текущей строки, пройдя некоторый путь. Для этого мы добавляем два столбца path и cycle и получаем запрос, защищённый от зацикливания:

WITH RECURSIVE search_graph(id, link, data, depth, path, cycle) AS ( SELECT g.id, g.link, g.data, 1, ARRAY[g.id], false FROM graph g UNION ALL SELECT g.id, g.link, g.data, sg.depth + 1, path || g.id, g.id = ANY(path) FROM graph g, search_graph sg WHERE g.id = sg.link AND NOT cycle ) SELECT * FROM search_graph;

Помимо предотвращения циклов, значения массива часто бывают полезны сами по себе для представления « пути » , приведшего к определённой строке.

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

WITH RECURSIVE search_graph(id, link, data, depth, path, cycle) AS ( SELECT g.id, g.link, g.data, 1, ARRAY[ROW(g.f1, g.f2)], false FROM graph g UNION ALL SELECT g.id, g.link, g.data, sg.depth + 1, path || ROW(g.f1, g.f2), ROW(g.f1, g.f2) = ANY(path) FROM graph g, search_graph sg WHERE g.id = sg.link AND NOT cycle ) SELECT * FROM search_graph;

Подсказка

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

Подсказка

Этот алгоритм рекурсивного вычисления запроса выдаёт в результате узлы, упорядоченные по пути погружения. Чтобы получить результаты, отсортированные по глубине, можно добавить во внешний запрос ORDER BY по столбцу « path » , полученному, как показано выше.

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

WITH RECURSIVE t(n) AS ( SELECT 1 UNION ALL SELECT n+1 FROM t ) SELECT n FROM t LIMIT 100;

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

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

Примеры выше показывают только предложение WITH с SELECT , но таким же образом его можно использовать с командами INSERT , UPDATE и DELETE . В каждом случае он по сути создаёт временную таблицу, к которой можно обратиться в основной команде.

7.8.2. Изменение данных в WITH

В предложении WITH можно также использовать операторы, изменяющие данные ( INSERT , UPDATE или DELETE ). Это позволяет выполнять в одном запросе сразу несколько разных операций. Например:

WITH moved_rows AS ( DELETE FROM products WHERE "date" >= '2010-10-01' AND "date" < '2010-11-01' RETURNING * ) INSERT INTO products_log SELECT * FROM moved_rows;

Этот запрос фактически перемещает строки из products в products_log . Оператор DELETE в WITH удаляет указанные строки из products и возвращает их содержимое в предложении RETURNING ; а затем главный запрос читает это содержимое и вставляет в таблицу products_log .

Следует заметить, что предложение WITH в данном случае присоединяется к оператору INSERT , а не к SELECT , вложенному в INSERT . Это необходимо, так как WITH может содержать операторы, изменяющие данные, только на верхнем уровне запроса. Однако при этом применяются обычные правила видимости WITH , так что к результату WITH можно обратиться и из вложенного оператора SELECT .

Операторы, изменяющие данные, в WITH обычно дополняются предложением RETURNING (см. Раздел 6.4), как показано в этом примере. Важно понимать, что временная таблица, которую можно будет использовать в остальном запросе, создаётся из результата RETURNING , а не целевой таблицы оператора. Если оператор, изменяющий данные, в WITH не дополнен предложением RETURNING , временная таблица не создаётся и обращаться к ней в остальном запросе нельзя. Однако такой запрос всё равно будет выполнен. Например, допустим следующий не очень практичный запрос:

WITH t AS ( DELETE FROM foo ) DELETE FROM bar;

Он удалит все строки из таблиц foo и bar . При этом число задействованных строк, которое получит клиент, будет подсчитываться только по строкам, удалённым из bar .

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

WITH RECURSIVE included_parts(sub_part, part) AS ( SELECT sub_part, part FROM parts WHERE part = 'our_product' UNION ALL SELECT p.sub_part, p.part FROM included_parts pr, parts p WHERE p.part = pr.sub_part ) DELETE FROM parts WHERE part IN (SELECT part FROM included_parts);

Этот запрос удаляет все непосредственные и косвенные составные части продукта.

Операторы, изменяющие данные в WITH , выполняются только один раз и всегда полностью, вне зависимости от того, принимает ли их результат основной запрос. Заметьте, что это отличается от поведения SELECT в WITH : как говорилось в предыдущем разделе, SELECT выполняется только до тех пор, пока его результаты востребованы основным запросом.

Вложенные операторы в WITH выполняются одновременно друг с другом и с основным запросом. Таким образом, порядок, в котором операторы в WITH будут фактически изменять данные, непредсказуем. Все эти операторы выполняются с одним снимком данных (см. Главу 13), так что они не могут « видеть » , как каждый из них меняет целевые таблицы. Это уменьшает эффект непредсказуемости фактического порядка изменения строк и означает, что RETURNING — единственный вариант передачи изменений от вложенных операторов WITH основному запросу. Например, в данном случае:

WITH t AS ( UPDATE products SET price = price * 1.05 RETURNING * ) SELECT * FROM products;

внешний оператор SELECT выдаст цены, которые были до действия UPDATE , тогда как в запросе

WITH t AS ( UPDATE products SET price = price * 1.05 RETURNING * ) SELECT * FROM t;

внешний SELECT выдаст изменённые данные.

Неоднократное изменение одной и той же строки в рамках одного оператора не поддерживается. Иметь место будет только одно из нескольких изменений и надёжно определить, какое именно, часто довольно сложно (а иногда и вовсе невозможно). Это так же касается случая, когда строка удаляется и изменяется в том же операторе: в результате может быть выполнено только обновление. Поэтому в общем случае следует избегать подобного наложения операций. В частности, избегайте подзапросов WITH , которые могут повлиять на строки, изменяемые основным оператором или операторами, вложенные в него. Результат действия таких запросов будет непредсказуемым.

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

Пред.	Наверх	След.
7.7. Списки VALUES	Начало	Глава 8. Типы данных

Postgresql какие запросы выполняются

Подзапросы (subquery) представляют такие запросы, которые могут быть встроены в другие запросы.

Например, определим таблицы для товаров, покупателей и заказов:

CREATE TABLE Products ( Id SERIAL PRIMARY KEY, ProductName VARCHAR(30) NOT NULL, Company VARCHAR(20) NOT NULL, ProductCount INTEGER DEFAULT 0, Price NUMERIC NOT NULL ); CREATE TABLE Customers ( Id SERIAL PRIMARY KEY, FirstName VARCHAR(30) NOT NULL ); CREATE TABLE Orders ( Id SERIAL PRIMARY KEY, ProductId INTEGER NOT NULL REFERENCES Products(Id) ON DELETE CASCADE, CustomerId INTEGER NOT NULL REFERENCES Customers(Id) ON DELETE CASCADE, CreatedAt DATE NOT NULL, ProductCount INTEGER DEFAULT 1, Price NUMERIC NOT NULL );

Таблица Orders содержит ссылки на две другие таблицы через поля ProductId и CustomerId.

Добавим в эти таблицы некоторые данные:

INSERT INTO Products(ProductName, Company, ProductCount, Price) VALUES ('iPhone X', 'Apple', 2, 66000), ('iPhone 8', 'Apple', 2, 51000), ('iPhone 7', 'Apple', 5, 42000), ('Galaxy S9', 'Samsung', 2, 56000), ('Galaxy S8 Plus', 'Samsung', 1, 46000), ('Nokia 9', 'HDM Global', 2, 26000), ('Desire 12', 'HTC', 6, 38000); INSERT INTO Customers(FirstName) VALUES ('Tom'), ('Bob'),('Sam'); INSERT INTO Orders(ProductId, CustomerId, CreatedAt, ProductCount, Price) VALUES ( (SELECT Id FROM Products WHERE ProductName='Galaxy S9'), (SELECT Id FROM Customers WHERE FirstName='Tom'), '2017-07-11', 2, (SELECT Price FROM Products WHERE ProductName='Galaxy S9') ), ( (SELECT Id FROM Products WHERE ProductName='iPhone 8'), (SELECT Id FROM Customers WHERE FirstName='Tom'), '2017-07-13', 1, (SELECT Price FROM Products WHERE ProductName='iPhone 8') ), ( (SELECT Id FROM Products WHERE ProductName='iPhone 8'), (SELECT Id FROM Customers WHERE FirstName='Bob'), '2017-07-11', 1, (SELECT Price FROM Products WHERE ProductName='iPhone 8') );

При добавлении данных в таблицу Orders как раз используются подзапросы. Например, первый заказ был сделан покупателем Tom на товар Galaxy S9. Поэтому в таблицу Orders необходимо сохранить информацию о заказе, где поле ProductId указывает на Id товара Galaxy S9, поле Price - на его цену, а поле CustomerId - на Id покупателя Tom. Но на момент написания запроса нам может быть неизвестен ни Id покупателя, ни Id товара, ни цена товара. В этом случае можно выполнить подзапрос.

Подзапрос представляет команду SELECT и заключается в скобки. В данном же случае при добавлении одного товара выполняется три подзапроса. Каждый подзапрос возвращает одно скалярное значение, например, идентификатор товара или покупателя.

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

SELECT * FROM Products WHERE Price = (SELECT MIN(Price) FROM Products);

Или найдем товары, цена которых выше средней:

SELECT * FROM Products WHERE Price > (SELECT AVG(Price) FROM Products);

Коррелирующие подзапросы

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

Но кроме того есть коррелирующие подзапросы (correlated subquery), результаты которых зависят от строк, которые извлекаются в основном запросе.

Например, выберем все заказы из таблицы Orders, добавив к ним информацию о товаре:

SELECT CreatedAt, Price, (SELECT ProductName FROM Products WHERE Products.Id = Orders.ProductId) AS Product FROM Orders;

Здесь для каждой строки из таблицы Orders будет выполняться подзапрос, результат которого зависит от столбца ProductId. И каждый подзапрос может возвращать различные данные.

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

SELECT ProductName, Company, Price, (SELECT AVG(Price) FROM Products AS SubProds WHERE SubProds.Company=Prods.Company) AS AvgPrice FROM Products AS Prods WHERE Price > (SELECT AVG(Price) FROM Products AS SubProds WHERE SubProds.Company=Prods.Company)

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

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

Чтобы избежать двойственности при фильтрации в подзапросе при сравнении производителей ( SubProds.Company=Prods.Company ) для внешней выборки установлен псевдоним Prods, а для выборки из подзапросов определен псевдоним SubProds.