Рекурсивные (Иерархические) запросы в PostgreSQL. Postgresql запросы

Рекурсивные (Иерархические) запросы в PostgreSQL / Хабр

Вслед за Ораклом со своим ‘connet by prior ‘ все остальные СУБД вводят свои реализации иерархических запросов (ИЗ). Хотелось бы рассказать широкой аудитории как это сделано в PostgreSQL.

История

Начиналось все вот так From: Evgen Potemkin <[email protected]> Subject: Proposal of hierachical queries (a la Oracle) Date: 2002-11-14 11:52:28 GMT Hi there! I want to propose the patch for adding the hierarchical queries posibility. It allows to construct queries a la Oracle for ex: SELECT a,b FROM t CONNECT BY a PRIOR b START WITH cond;B I've seen this type of queries often made by adding a new type, which stores position of row in the tree. But sorting such tree are very tricky (i think). Patch allows result tree to be sorted, i.e. subnodes of each node will be sorted by ORDER BY clause. with regards, evgen

затем

From: Tatsuo Ishii <[email protected]> Subject: RFP: Recursive query in 8.4 Date: 2008-02-19 08:36:00 GMT (1 year, 12 weeks, 6 days ago) Hi, As I promised before we would like to propose implementing the recursive query as defined in the standard for PostgreSQL 8.4. The work is supported by Sumitomo Electric Information Systems Co., Ltd. (http://www.sei-info.co.jp/) and SRA OSS, Inc. Japan (http://www.sraoss.co.jp).

Ну И начиная с 8.4 версии Postgresql стала поддерживать рекурсивный запрос.

Теория

ИЗ в PostgreSQL реализовано на базе стандратной SQL clause WITH. Давайте немного углубимся: не рекурсивный WITH позволяет удешивить повторяющиеся подзапросы, разделить сложный запрос на несколко меньших, является удобным так сказать ярлыком для обращения к подзапросу и само посебе удобно в плане экономии времени при написании кода. как в примере ниже удалось избежать использования подзапроса в WHERE за счет применения временой таблицы top_regions сформированой специально для этого запроса.

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;

* This source code was highlighted with Source Code Highlighter.

Добавление необязательного оператора RECURSEVE позволяет запросу в Postgre обращатся к своимже выходным данным. алгоритм запроса должен сосоять из друх частей. первая часть это основа, обычно возвращающий одну строку с исходной точкой иерархии или части иерархии. Тоесть место в иерархии откуда будет начат отсчет ( например корень). и вторя рекурсивная часть которая будет связыватся с временой таблицой которую мы объявили после WITH. объединяются первая и вторая части оператором UNION или UNION ALL. что бы этот сумбурный набор слов привести в порядок привидем пример. Создадим таблицу в которой будет описана структара одной компании CREATE TABLE KPO ( "ID" character varying(55), "DESCRIPTION" character varying(255), "PARENT" character varying(55 ) ;

после внесения туда данных: Select * from kpo

ID DESCRIPTION PARENT

== ======	================================	=======
KPO	KARACHAGANAK PETROLEUM OPERATING	{null}
AKSAY	AKSAY	KPO
URALSK	KPO	KPO
LONDON	LONDON	KPO
KPC	KPC	AKSAY
U2	UNIT-2	AKSAY
U3	UNIT-3	AKSAY
PROD	PRODACTION	KPC
MAINT	MAINTENANCE	AKSAY
CMMS	CMMS TEAM	MAINT

Теперь сам рекурсивный запрос:

WITH RECURSIVE temp1 ( "ID","PARENT","DESCRIPTION",PATH, LEVEL ) AS (

SELECT T1."ID",T1."PARENT", T1."DESCRIPTION", CAST (T1."ID" AS VARCHAR (50)) as PATH, 1
FROM KPO T1 WHERE T1."PARENT" IS NULL
union
select T2."ID", T2."PARENT", T2."DESCRIPTION", CAST ( temp1.PATH ||'->'|| T2."ID" AS VARCHAR(50)) ,LEVEL + 1
FROM KPO T2 INNER JOIN temp1 ON( temp1."ID"= T2."PARENT") )
select * from temp1 ORDER BY PATH LIMIT 100

* This source code was highlighted with Source Code Highlighter.

Первая часть (строки 2-3) возвращает во временую таблицу первую строку в данном случае корневую запись наешей структуры, от которой будет начинатся отсчет в нашей иерархии. вторая часть( строки 4-5) добавляет в эту же временую таблицу записи связаные с уже содеражащейся в temp1 строкой через JOIN (ID = PARENT) и так до конца пока все листья нашего ROOTa не окажутся в temp1. Так же в даном примере была сыметирована Ораколавская функция sys_connect_by_path.

«ID» «PARENT» «DESCRIPTION» «path» «level»

KPO		KARACHAGANAK PETROLEUM OPERATING	KPO	1
AKSAY	KPO	AKSAY	KPO->AKSAY	2
KPC	AKSAY	KPC	KPO->AKSAY->KPC	3
PROD	KPC	PRODAUCTION	KPO->AKSAY->KPC->PROD	4
MAINT	AKSAY	MAINTENANCE	KPO->AKSAY->MAINT	3
CMMS	MAINT	CMMS TEAM	KPO->AKSAY->MAINT->CMMS	4
U2	AKSAY	UNIT-2	KPO->AKSAY->U2	3
U3	AKSAY	UNIT-3	KPO->AKSAY->U3	3
LONDON	KPO	LONDON	KPO->LONDON	2
URALSK	KPO	URALSK	KPO->URALSK	2

В Postgre нет встроеной проверки на зацикливание, поэтому если данные мы получили от ребят которые занимались непосредствено созданием структуры в Excel, мы должны проверить эту структуру на целостность. иногда достаточно использовать UNION вместо UNION ALL но это только иногда. если вы в первой части задали отправную точку в иерархии и елси даже гдето в иерархии есть обрывы в принципе зпустив вышеупомянутый квери ошибки не будет, просто строки «отщипенцы» будут проигнорированы. Но нам же надо знать где ошибка, и реализовать это можно внедрив дополнительную проверку перед выполнением UNION.

WITH RECURSIVE temp1 ( "ID","PARENT","DESCRIPTION",PATH, LEVEL, cycle ) AS (
SELECT T1."ID",T1."PARENT", T1."DESCRIPTION", cast (array[T1."ID"] as varchar(100)[]) , 1 , FALSE
FROM KPO T1
union all
select T2."ID", T2."PARENT", T2."DESCRIPTION", cast(temp1.PATH || T2."ID" as varchar(100) []) ,LEVEL + 1 ,
T2."ID" = any (temp1.PATH)
FROM KPO T2 INNER JOIN temp1 ON( temp1."ID"= T2."PARENT") AND NOT CYCLE )
select * from temp1 WHERE CYCLE IS TRUE LIMIT 100;

* This source code was highlighted with Source Code Highlighter.

Здесь как видете создается такое же поле Path но уже все предшествующие родители организованны в массиве, что дает нам возможно сравнивать нам каждуй новый “ID” на дубликат ну и если в массиве уже есть такая запись тогда во временную таблицу строка заносится с флагом и в следущий проход мы уже не используюм эту строку для поиска потомков, благодоря этому избегается зацикливанияе (union all… WHERE … AND NOT CYCLE).

Совет с официального сайта: используйте оператор LIMIT как это делал я в примерах. это поможет вам сохранить нервы.

Практические примеры

Теория хоршо конечно, но где все это можно применить на практике, темболле в свете стоимости таких запросов. Кроме того что бы красиво нарисовать иерархию в одном запросе, и найти например все листья токогото “ID” есть еще и другие задачи. Часто вам надо сделать изменения, такие как например изменить код телефона всем сотрудникам определенного департамента в телефонном справочнике. конечно можно использовать колонку в таблице работников или даже сделать префикс к “ID”. но все это делает базу не гибкой и не маштабируемой. Намного лучше сделать отдельную таблицу Работники которая будет отображать иерархию с тремя колонками “ID “, “PARENT “, “HIERARCHID”. поле “HIERARCHID” позволит сделать вам болле чем одну иерархическую структуру. Таблицу назовем для примера ANCESTOR которая будет тоже состоять из трех колонок “ID”, “ANCESTOR”, “HIERARCHID”. в поле “ANCESTOR” будут содержатся все предки, помните массив «Path» из примера 3. так вот заполнить эту таблицу можно как раз с помощью рекурсивного запроса.

insert into ancestor ( “ID” "ANCESTOR", "HIERARCHID")
WITH RECURSIVE temp1 ( "ID","PARENT","DESCRIPTION",PATH, LEVEL, cycle ) AS (
SELECT T1."ID",T1."PARENT", T1."DESCRIPTION", cast (array[T1."ID"] as varchar(100)[]) , 1 , FALSE
FROM KPO T1
union all
select T2."ID", T2."PARENT", T2."DESCRIPTION", cast(temp1.PATH || T2."ID" as varchar(100) []) ,LEVEL + 1 ,
T2."ID" = any (temp1.PATH)
FROM KPO T2 INNER JOIN temp1 ON( temp1."ID"= T2."PARENT") AND NOT CYCLE
)
select "ID" AS "LOCATION", PATH[1] AS "ANCESTOR" , 'DEPT' AS "DID" from temp1

* This source code was highlighted with Source Code Highlighter.

получится вот такая табличка

LOCATION ANCESTOR HIERARCHID

=========	=========	==========
AKSAY	AKSAY	DEPT
AKSAY	KPO	DEPT
CMMS	KPO	DEPT
CMMS	MAINT	DEPT
CMMS	CMMS	DEPT
CMMS	AKSAY	DEPT
KPC	AKSAY	DEPT
KPC	KPC	DEPT
KPC	KPO	DEPT
KPO	KPO	DEPT
LONDON	LONDON	DEPT
LONDON	KPO	DEPT
MAINT	AKSAY	DEPT
MAINT	MAINT	DEPT
MAINT	KPO	DEPT
PROD	KPO	DEPT
PROD	AKSAY	DEPT
PROD	KPC	DEPT
PROD	PROD	DEPT
U2	AKSAY	DEPT
U2	KPO	DEPT
U2	U2	DEPT
U3	U3	DEPT
U3	AKSAY	DEPT
U3	KPO	DEPT
URALSK	KPO	DEPT
URALSK	URALSK	DEPT

Теперь уже все наши селекты и апдейты будем делать используя эту таблицу. например с темеже телефонными номерами это будет выглядеть примерно так:

Update EMPLOYEE SET “TELNUM” = ‘545-454’ FROM ANCESTOR WHERE “ANCESTOR”.”ANCESTOR” = ‘AKSAY’ AND EMPLOYEE.”LOCATION” = ANCESTOR.”LOCATION” AND EMPLOYEE.ISDPRT = ‘N’ ;

Да еще надо будет предусматреть тригер на обновление таблицы при внесении новой или удалении записи в таблице KPO.

Продолжим,

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

WITH RECURSIVE t(n) AS (
VALUES (1)
UNION ALL
SELECT n+1 FROM t WHERE n < (SELECT cast (extract ('day' from date_trunc('month',current_date) - interval '24 hour') as integer))
)
SELECT to_date ( n || '-'||extract ('month' from date_trunc('month',current_date) - interval '24 hour')
|| '-'||extract ('year' from date_trunc('month',current_date) - interval '24 hour'), 'dd-mm-yyyy')
FROM t;

* This source code was highlighted with Source Code Highlighter.

Еще один не очень полезный пример на закуску. Оригинальная идея Грейм Джоба (Graeme Job). результат лучше смотреть в текстовом файле.

WITH RECURSIVE z(ix, iy, cx, cy, x, y, i) AS (
SELECT ix, iy, x::float, y::float, x::float, y::float, 0
FROM (select -1.88+0.016*i, i from generate_series(0,150) as i) as xgen(x,ix),
(select -1.11+0.060*i, i from generate_series(0,36) as i) as ygen(y,iy)
UNION ALL
SELECT ix, iy, cx, cy, x*x - y*y + cx, y*x*2 + cy, i+1
FROM z
WHERE x * x + y * y < 16::float
AND i < 27
)
SELECT array_to_string(array_agg(substring(' .,,,-----++++%%%%@@@@#### ',
greatest(i,1), 1)),'')
FROM (
SELECT ix, iy, max(i) AS i
FROM z
GROUP BY iy, ix
ORDER BY iy, ix
) AS zt
GROUP BY iy
ORDER BY iy

* This source code was highlighted with Source Code Highlighter.

ну вот такой пост для начала ;-)

habr.com

Заметки разработчика: Рекурсивный запрос на postgres

Актуальная версия этой статьи на моём новом сайте devmark.ru

Рассмотрим составление рекурсивных запросов на PostgreSQL для иерархических данных на примере следующей таблицы:

CREATE TABLE hierarchy_example( id serial NOT NULL, name character varying(100), parent_id integer, CONSTRAINT id_pk PRIMARY KEY (id )) Заполним её данными: parent_id содержит номер записи, которая является родительской по отношению к данной. Если parent_id = null, считаем, что это - корневой элемент иерархии.

Теперь составим запрос для прохода по этой иерархии, от элемента с именем subitem1 до root. На каждой итерации будем добавлять новую строку во временную таблицу temp1.

WITH RECURSIVE temp1 ( id, parent_id, name, path ) AS (SELECT T1.id, T1.parent_id, T1.name, CAST (T1.name AS VARCHAR (50)) as PATH FROM hierarchy_example T1 WHERE T1.id = 4unionselect T2.id, T2.parent_id, T2.name, CAST ( temp1.PATH ||'->'|| T2.name AS VARCHAR(50)) FROM hierarchy_example T2 INNER JOIN temp1 ON (temp1.parent_id = T2.id))select * from temp1

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

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

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

Результат выполнения запроса:

Как видим, в столбце path последовательно отображается путь от subitem1 до root. Также обратите внимание, что в столбце name нет элемента item2 - он не входит в данную ветвь иерархии.

developer-remarks.blogspot.com

Запросы к таблицам | PostgreSQL

Для получения данных из какой-либо таблицы, к этой таблице осуществляется запрос. Для этого используется оператор SQL SELECT. Этот оператор подразделяется на список выбора (часть, где перечисляются возвращаемые запросом поля), список таблиц (часть, где перечисляются таблицы, из которых выбираются данные) и необязательную часть отбора (часть, где указываются разные ограничения). Например, чтобы получить все записи таблицы weather введите:

SELECT * FROM weather;

Здесь * означает "все колонки"). So the same result would be had with:

SELECT city, temp_lo, temp_hi, prcp, date FROM weather;

Вывод должен выглядеть так:

city | temp_lo | temp_hi | prcp | date ---------------+---------+---------+------+------------ San Francisco | 46 | 50 | 0.25 | 1994-11-27 San Francisco | 43 | 57 | 0 | 1994-11-29 Hayward | 37 | 54 | | 1994-11-29 (3 rows)

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

SELECT city, (temp_hi+temp_lo)/2 AS temp_avg, date FROM weather;

Что приведет к выводу:

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

Запрос может быть "уточнён" с помощью добавления ключевого слова WHERE, в котором задаётся какие строки вы хотите получить. WHERE содержит Логическое (истина или ложь) выражение и в результат попадут только строки, для которых Логическое выражение является истинным. В запросе, в части отбора, разрешаются полезные Логические операторы (AND, OR и NOT). Например, следующий запрос получает погоду в Сан-Франциско в дождливые дни:

SELECT * FROM weather WHERE city = 'San Francisco' AND prcp > 0.0;

Результат:

city | temp_lo | temp_hi | prcp | date ---------------+---------+---------+------+------------ San Francisco | 46 | 50 | 0.25 | 1994-11-27 (1 row)

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

SELECT * FROM weather ORDER BY city; city | temp_lo | temp_hi | prcp | date ---------------+---------+---------+------+------------ Hayward | 37 | 54 | | 1994-11-29 San Francisco | 43 | 57 | 0 | 1994-11-29 San Francisco | 46 | 50 | 0.25 | 1994-11-27

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

SELECT * FROM weather ORDER BY city, temp_lo;

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

SELECT DISTINCT city FROM weather; city --------------- Hayward San Francisco (2 rows)

Здесь снова, сортировка строк результата может быть произвольной. Вы можете обеспечить нужный результат, используя DISTINCT и ORDER BY вместе:

SELECT DISTINCT city FROM weather ORDER BY city;

postgresql.ru.net

PostgreSQL рекурсивный запрос

Рассмотрим создание рекурсивного запроса на Postgresql .

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

Общая схема рекурсивного запроса:

WITH RECURSIVE t AS ( нерекурсивная часть (1) UNION ALL рекурсивная часть (2))SELECT * FROM t; (3)

Чтобы не мучать Вас теорией, перейдем сразу к практике. С помощью рекурсивного запроса, можно вывести сумму чисел от 1 до 10.

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

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

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

Приведу пример из моей практики: "Расчет сальдовки с помощью postgresql".

Работал я еще тогда мидл-разработчиком, в достаточно известной фирме ООО "Эттон" в городе Казани. Занималась данная компания автоматизацией ЖКХ. Наша команда разрабатывала продукт "Регион", для ведения капитального ремонта. Я в частности отвечал, за модуль "Биллинг".

Модуль "Биллинг" обрабатывал данные по собственникам в БД под управлением PostgreSQL. Обработка данных происходила на стороне сервера, с помощью микросервисов. И чтобы получить данные по входящему и исходящему сальдо, для отчетов. Нам разработчикам, приходилось писать запрос, обрабатывать данные на сервере, и добавлять уже обработанные данные по расчитанной сальдовке.

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

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

Период	Входящее сальдо	Начислено	Оплачено	Пени	Исходящее сальдо
февраль 2017	0.00	100.00	60.00	0.00	40.00
март 2017	40.00	100.00	0.00	0.00	140.00
апрель 2017	140.00	100.00	0.00	3.00	243.00

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

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

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

Ниже приведу, с какими проблемами я столкнулся при написании запроса, и как их решил:

Приведу упрощенную схему структуры таблицы "billing_bill_account_operation", где хранятся данные по начислениям собствеников.

Для облегчения понимания, как писать запрос, разделим его на несколько этапов.

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

Получим следующие данные:

account_id	period	in_saldo	credited	paid	peni	out_saldo
1	2017-01-01	0	300.00	150.00	0.00	450.00
2	2017-01-01	0	240.00	0.00	0.00	240.00
3	2017-01-01	0	180.00	0.00	0.00	180.00

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

Данный запрос вернет данные за следующий месяц, то есть за февраль 2017 года.

account_id	period	credited	paid	peni
1	2017-02-01	0.00	50.00	0.00
2	2017-02-01	80.00	0.00	0.00
3	2017-02-01	60.00	0.00	0.00

А теперь, третьим этапом, попробуем совершить "магию", соединить нерекурсивную часть, с рекурсивной частью.

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

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

В итоге, получим следующие данные:

account_id	period	in_saldo	credited	paid	peni	out_saldo
1	2017-01-01	0	300.00	150.00	0.00	450.00
1	2017-02-01	450.00	0.00	50.00	0.00	500.00
2	2017-01-01	0	240.00	0.00	0.00	240.00
2	2017-02-01	240.00	80.00	0.00	0.00	320.00
2	2017-03-01	320.00	80.00	0.00	2.00	402.00
3	2017-01-01	0	180.00	0.00	0.00	180.00
3	2017-02-01	180.00	60.00	0.00	0.00	240.00

При написании статьи, были использованы следующие ресурсы:

https://postgrespro.ru/docs/postgrespro/9.6/queries-with

https://habrahabr.ru/company/postgrespro/blog/318398/

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

www.tyzh-programmist.ru

Медленные запросы в PostgreSQL

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

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

SELECT pid, client_addr, usename, datname, state, waiting, to_char(current_timestamp - state_change, 'SSSS.MS') AS runtime, query FROM pg_stat_activity WHERE pid <> pg_backend_pid() AND state = 'active' AND state_change < current_timestamp - INTERVAL '3' SECOND ORDER BY runtime DESC;

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

-- вычисляет длительность выполнения запроса и форматирует его в простой читаемый вид 1.234 (сек) to_char(current_timestamp - state_change, 'SSSS.MS') AS runtime, -- исключает из выдачи текущий запрос pid <> pg_backend_pid() -- отображает только активные запросы AND state = 'active' -- отображает только запросы которые выполняются более 3-х секунд AND state_change < current_timestamp - INTERVAL '3' SECOND

Если ваши запросы очень длинные и не отображаются полностью, но вам необходимо видеть их целиком, измените значение параметра track_activity_query_size в конфигурационном файле БД или параметрах RDS.

track_activity_query_size=16384

Данная настройка позволит логировать запросы длинной до 16KB, но имейте в виду, что данный параметр является статическим и вступит в силу, только после перезагрузки БД.

Отмена или уничтожение запросов

После обнаружения медленного запроса, можно отменить его используя pg_cancel_backend(pid) или уничтожить при помощи pg_terminate_backend(pid).

Пример запроса уничтожающего все запросы зависшие на более чем 1 минуту в указанной БД:

SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE pid <> pg_backend_pid() AND datname = 'ИМЯ_ВАШЕЙ_БД' AND state = 'idle' AND state_change < current_timestamp - INTERVAL '1' MINUTE;

Успешного обнаружения!

artemenko.ru

Производительность запросов в PostgreSQL – шаг за шагом

Илья Космодемьянский (hydrobiont )

Для начала сразу пару слов о том, о чем пойдет речь. Во-первых, что такое оптимизация запросов? Люди редко формулируют и, бывает так, что часто недооценивают понимание того, что они делают. Можно пытаться ускорить какой-то конкретный запрос, но это не обязательно будет оптимизацией. Мы немного на эту тему потеоретизируем, потом поговорим о том, с какого конца к этому вопросу подходить, когда начинать оптимизировать, как это делать, и как понять, что какой-то запрос или набор запросов никак нельзя оптимизировать – такие случаи тоже бывают, и тогда нужно просто переделывать. Как ни странно, я почти не буду приводить примеров того, как запросы оптимизировать, потому что даже 100 примеров не приблизят нас к разгадке.

Обычно самое узкое место – это непонимание, с какой стороны подойти к этому делу. Все говорят: «Читай EXPLAIN, проверяйте запросы EXPLAIN'ом», но мне часто задают вопросы о том, на что там смотреть и что делать после того, как на EXPLAIN уже посмотрели? Вот об этом я постараюсь рассказать подробнее.

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

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

Что значит «оптимизировать запросы»? Как правило, у вас такой задачи не возникает. Проблема обычно заключается в том, что «все плохо!». Мы пишем наш замечательный проект, все работает, все довольны, но в какой-то момент заказали чуть больше рекламы, пришло чуть больше пользователей, и все упало. Потому что, когда разрабатывают проект быстро, обычно используют «рельсы», «джанго» и т.п. и пишут «в лоб» – важно быстрее дать продукт. Это на самом деле правильно, т.к. никому не нужен идеально «вылизанный» проект, который не работает. Дальше нужно понять, что происходит. Эти медленные запросы, которые мы хотим оптимизировать, – это просто такой интерфейс, который выдает наружу, что вот это тормозит, но причина запросто может быть в чем-то другом. Может быть плохое «железо», может быть ненастроенная база, и в этот момент начинать с оптимизации запросов, в принципе, не стоит. Сначала надо посмотреть, что происходит с БД.

Бывают просто грубые ошибки в настройке БД и до того, как они будут исправлены, оптимизировать запросы бесполезно, потому что bottleneck в другом месте. Например, если речь о Postgres’е, у вас может быть отключен autovacuum. Почему-то люди это иногда делают (этого ни в коем случае делать нельзя!), но когда он отключен, у вас очень большая фрагментация таблицы. Легко может быть таблица на 100 тыс. записей размером с таблицу в 1 млрд. записей. Естественно, любые запросы к ней будут медленнее, чем вы ожидаете. Поэтому сначала нужно БД настроить, проверить, что все хорошо работает.

Еще одна частая ошибка, когда 1000 worker ‘ов Postgres’а работают, потому что очень много подключений от приложения, и нет никакого Connection Broker’а. Надо понимать, что если у вас 500 connection’ов, то у вас должно быть 500 ядер на сервере, на котором вы работаете. В противном случае эти connection’ы будут друг другу мешать и будут все время проводить в ожидании. Когда вы эти глупости исправили (их может быть довольно много, но основных – 5-10 штук – правильные настройки памяти, диска, autovacuum...), вы можете переходить к оптимизации запросов. И только тогда. Не надо пытаться оптимизировать то, что еще вы только делаете.

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

Очень важный момент – знать, какие запросы оптимизировать? Если вы будете оптимизировать все подряд, то есть шанс не угадать, какие из запросов являются самыми проблемными. Вы просто потратите много времени и с большой вероятностью не доберетесь до нужных задач. Поэтому запросы нужно оптимизировать по мере поступления проблем. Посмотрите, где работать перестало, стало медленно и плохо – вот этот кусок и оптимизируйте. Если у вас есть что-то, что редко используется, не надо этого трогать, не тратьте время.

По отобранному топу берутся запросы, и смотрится, что с ними можно сделать. Для этого, особенно в версии 9.4, правильный способ – использовать EXTENSION pg_stat_statements, который все это делает «на лету» в онлайне, и можно на все это посмотреть.

Давайте разберемся с этим детально.

По адресу внизу страницы – наши pg-utils, которые доступны в свободном доступе, можно скачать и воспользоваться. В папке лежит некая обвязка вокруг стандартного контриба pg_stat_statements, который позволяет генерить вот такие отчеты за сутки работы на БД, и мы можем посмотреть, что там происходило. Видим, соответственно, некий топ запросов, каким-то образом ранжированный.

Нам важно знать, что есть первая позиция, вторая (обычно их еще десяток), и мы видим, что по каким-то параметрам у нас некий запрос выходит на первое место – он занимает, например, 24% нагрузки базы. Это довольно много и это надо как-то оптимизировать. Вы смотрите на запрос и думаете: «А сколько он денег проекту приносит?». Если он приносит проекту очень много денег, то пусть даже половину нагрузки отъедает, а если он денег не приносит, а отъедает половину ресурсов – это плохо, с этим надо что-то сделать. Таким образом, вы смотрите на топ запросов за предыдущий день и размышляете, что с этим делать.

Хорошей практикой у нас считается, когда есть команда разработчиков, которая делает что-то на проекте, и раз в сутки по cron'у такой отчет приходит всем DBA, всем разработчикам, всем админам.

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

Таким образом, время запроса – вещь относительная, и тут нужно смотреть, насколько часто этот запрос работает. Если это отдача чего-то на Главной странице, и этот запрос занимает 1 секунду, надо понимать, что у вас будет эта секунда и еще плюс оверхед от всего того, что нужно, чтобы сформировать эту страницу. Это значит, что пользователь увидит результат гарантированно медленнее, чем через секунду, и для онлайнового высоконагруженного веба это неприемлемые результаты. Если же запрос у вас для какой-то аналитики гоняется ночью, присылается кому-то асинхронный отчет, то, вероятно, он может себе позволить работать медленно. То есть, всегда надо знать свои данные и всегда думать, сколько времени допустимо, чтобы этот запрос работал. Опять же важен характер нагрузки на базе. Например, у вас есть длинный тяжелый запрос, вы его гоняете в пиковое время, а это запрос для какой-то аналитики, для менеджеров и т.п.

Посмотрите на профиль нагрузки на базу. У вас есть pg_stat_statements, по нему вы можете увидеть, топ медленных запросов, например, с 2х до 4х часов дня, и в это время не гонять длинные аналитические запросы.

Не забываем о том, сколько этот вопрос приносит денег и имеет ли он право занимать много ресурсов БД. Если вы сделали прикольную фичу, которая не зарабатывает для проекта ничего, а этот запрос съедает 50% ресурсов, то значит, вы написали плохой запрос и вам нужно переделать эту идею и даже иногда объяснить менеджеру, почему эта технически очень сложная штука просто съедает ресурсы. Все говорят, мол, хочу, чтобы Золотая Рыбка была у меня на посылках, тем не менее, сервер – он железный, он имеет некие лимиты, и резиново растягивать его нельзя. Люди, которые делают облака, скажут вам, что можно, но я как зануда-админ, скажу, что нельзя.

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

Кто писал на Ruby, использовал всякие хитрые ORM'ы, знает, что вот по этому запросу можно опознать Django. Это фирменный стиль, почерк радиста ни с чем не перепутаешь.

Как вы думаете, какой максимальной длины in-список я видел в своей жизни? Надо считать в гигабайтах! Если не смотреть на то, что делает ваш ORM, то легко может получить несколько гигабайт, и этот запрос никогда не исполнится. Это плохо и означает, что у вас совершенно неоптимальный доступ к данным. Этот запрос плох еще по многим причинам, но основная причина того, что он может быть такой длины, которая в Postgres никогда в жизни не пролезет.

Второй момент – это парсинг. Можно написать очень витиеватый запрос, который просто будет долго парситься. В новой версии Postgres’а, если я не ошибаюсь, будет в EXPLAIN ‘е время парсинга, и можно будет понять, сколько на это уходит времени. Сейчас можно просто сделать EXPLAIN и посмотреть таймингом, соответственно, сколько у нас ушло на исполнение запроса, а сколько – на парсинг.

Потом запрос нужно оптимизировать. И это далеко не такая простая задача, как кажется, потому что оптимизатор – это достаточно сложные алгоритмы. Вот, к примеру, вы хотите сделать Join двух таблиц. Он берет одну таблицу, выбирает метод доступа к нужным данным и при-Join'ивает к ней следующую. Если у вас еще один Join с еще одной таблицей, то сначала он с-Join'ит две, потом с ResultSet’ом с-Join'ит еще одну. Если вы написали запрос, в котором 512 Join'ов, дальше начинается очень интересная «петрушка» с оптимизацией этого всего.

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

Далее может быть непосредственно исполнение. Если ваш запрос должен вернуть куда-то 10 Гб данных, сложно рассчитывать на то, что он будет работать миллисекунды. Никаким волшебством его не заставить. Поэтому, если вам нужно отдать много данных, то сразу имейте в виду, что волшебства не бывает. Оно бывает в мире NoSQL, а здесь его нет.

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

Самый главный слайд этой презентации:

Это EXPLAIN.

После того, как вы выделили топ запросов, вы как-то удостоверились, что эти запросы медленные, и хотите с ними что-то сделать, вам нужно прогнать EXPLAIN.

До этого этапа доходят многие, а дальше загвоздка. Люди жалуются: «Ну, мы посмотрели EXPLAIN, и что с ним дальше делать?». Вот это я сейчас вам и расскажу.

На слайде 2 EXPLAIN'а, немного разный синтаксис.

Можно вот так делать: explain (analyze on, buffers on), можно просто написать explain analyze, например.

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

Когда вы отобрали топ медленных запросов, правильно использовать EXPLAIN ANALYZE, потому что у вас может быть выбран неоптимальный план, может быть не собрана статистика и др.

Postgres – супер транзакционная штука, если у вас есть пишущий запрос, вы не хотите, чтобы эти результаты записались, пока вы что-то оптимизируете, говорите: «begin», запускаете запрос (но желательно смотреть, что, вообще, происходит – тяжелый запрос в пиковое время на боевую базу не всегда бывает хорошо), потом говорите «rallback», чтобы у вас эти данные не записались.

Здесь показываются некие цифры, часть которых принадлежит EXPLAIN'у, а часть – ANALYZE'у. Это важные цифры. В EXPLAIN'е есть условные «попугаи» под названием «cost». 1 cost в Postgres‘е по умолчанию – это время, которое затрачивается на извлечение одного блока размером 8 Кб при последовательном sequential scan'е. В принципе, эта величина зависит от машины, поэтому она условная, поэтому это удобно. Если у вас быстрые диски, это будет быстрее, если медленные – медленнее. Важно понимать, что cost=9.54 – это означает, что в 9.54 раза это будет медленнее, чем достать 1 блок размером 8 Кб.

При этом цифр две: первая означает, сколько пройдет времени до момента начала возврата первых результатов, а вторая – это сколько пройдет времени до того, как результат будет возвращен весь. Если вы извлекаете много данных, то первая цифра будет относительно маленькой, а вторая будет достаточно большой. Это актуальное время, сколько это на самом деле заняло. Если по каким-то причинам у вас cost очень маленький, а это время очень большое, значит у вас какие-то проблемы со сбором статистики, нужно проверить, включен ли autovacuum, потому что тот же самый демон autovacuum’а собирает еще и статистику для оптимизатора.

EXPLAIN – это такое дерево, есть нижние варианты, грубо говоря, как достать данные с диска – это скан табличек, скан индексов и т.д.; и более верхние варианты, когда на верх наслаивается какая-то агрегация, Join'ы и т.д. Когда вы смотрите на такой EXPLAIN, задача очень простая:

понять, насколько быстро он работает, посмотреть на runtime, посмотреть, сколько там чего происходило;
посмотреть, какой узел этого дерева самый дорогой. Если у вас на нижнем этапе сразу cost достаточно большой, actual time большой, то значит, вам этот кусок и надо оптимизировать. Например, если у вас сканируется таблица целиком, вам может там понадобится сделать индексы. Это то место, которое действительно нужно оптимизировать, на которое нужно смотреть.

Если у вас, например, чудит агрегация, как в нашем случае на слайде – она там более тяжелая, то вам нужно подумать, как от нее избавиться.

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

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

Если у вас вся таблица увешана индексами, которые не используются, с большой вероятностью вы можете часть из них снести, и будет быстрее. Тем не менее, если в вашем запросе нужно, например, извлечь половину данных из таблицы, с большой вероятностью ваш индекс не будет использоваться, потому что по индексу имеет смысл спозиционироваться в какое-то достаточно точное место и эти данные достать. Если вам нужно большую «простыню», которая сопоставима по размерам с таблицей, sequential scan самой таблицы будет всегда быстрее, чем index scan, потому что вам будет нужно сначала сделать index scan, потом еще одну операцию – достать данные.

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

В Postgres’е есть такой параметр – сессионная переменная, enable index scan установить в off или, наоборот, sequential scan установить в off, и вы можете посмотреть – с индексом или без него будет быстрее/медленнее. Оптимизировать так запросы «в бою» я бы не советовал, это очень жесткий «костыль» и очень серьезное ограничение функционала для оптимизатора, но поэкспериментировать, посмотреть – это полезно. Вы сделали запрос, сделали для него индекс, считаете, что он будет работать, отключите sequential scan, оптимизатор будет вынужден выбрать план с индексом, и посмотрите, не получилось ли медленнее, чем то, что Postgres предложил вам сам. В большинстве случаев это именно так.

Далее важно, как написан запрос. Если у нас будет что-то вроде этого – (where counter + 1 = 46) – индекс браться не будет, автоматически Postgres эту операцию сделать не может. Казалось бы, простое сложение, но с тем же успехом можно предложить оптимизатору еще и дифуры порешать. В Postgres’е большое количество типов данных, на них можно определять любые операторы, любые действия, например, алгебраические или др., и оптимизатор должен для всех этих типов знать, как это действие выполнять, а для него это слишком тяжелая задача, это никогда не будет работать.

Следующее – почему, например, Join работает плохо? Это один из важных узлов, его все используют.

Join'ы бывают разных типов, и я говорю не о LEFT, RIGHT, INNER и т.д., а об алгоритмах, как Join'ы выполняются. Postgres имеет три основных алгоритма Join'а, а именно – Nested Loop (название говорит само за себя – мы берем данные из одной таблицы и циклами их Join'им), Hash index (когда одна, чаще маленькая, таблица хэшируется и по этому хэшу Join'ится с другой таблицей) и Merge Join (который тоже очевидно, как работает).

Эти Join'ы не всегда одинаково полезны, оптимизатор может выбрать между ними. Например, у вас Join'ятся две таблицы, оптимизатор выбирает Hash Join, и вы понимаете, что он работает медленно, вас это не устраивает. Имеет смысл посмотреть, а индексированы ли у вас те поля, по которым вы Join'ите? Если у вас эти поля не индексированы, оптимизатор может не выбрать Nested Loop, который здесь очевидно выгоднее. Если вы создадите индекс, оптимизатор выберет Nested Loop, и все будет работать быстро.

Следующий момент – у вас оптимизатор из каких-то соображений выбирает Nested Loop, а вам кажется, что одна таблица очень маленькая, другая – очень большая и Hash Join там был бы очень уместен, потому что маленькую таблицу можно быстро прохэшировать и быстро с ней работать. Посмотрите, сколько у вас work mem'а. т.е. сколько памяти может занять один worker Postgres’а. Если эта таблица хэшируется в, например, 100 Мб, а у вас work mem'а выдано только 30 Мб, то worker будет работать медленно. Если вы добавите work mem'а и хэширование начнет вмещаться в память, оптимизатор выберет правильный Hash Join и будет быстро и хорошо.

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

Поскольку оптимизировать запросы нужно только «на бою» (потому что на тесте вы никогда не воспроизведете workload настолько же точно), то делать это надо с известной осторожностью.

Пример такой оптимизации:

Я уже показывал этот запрос и очень ругался на него. Одна из причин для такой ругани состоит в том, что, все-таки Postgres не совершенен, он – развивающаяся система, есть в нем какие-то недостатки (сообществу очень нужны разработчики оптимизатора, если вы хотите подконтрибьютить Postgres’у, то можете в эту сторону посмотреть, такие усилия всегда будут очень приветствоваться, потому что есть недоделки). Вот и случай такого длинного массива в WHERE очень распространенный, потому что многие ORM’ы это делают. По идее, Postgres должен как-то хэшировать этот массив и соответственно осуществлять в нем поиск. Вместо этого он его перебирает и получается достаточно противно, время растет очень существенно, и начинаются проблемы.

Посмотрим на EXPLAIN этого дела:

Мы видим, что фильтр без хэша работает плохо, несмотря на то, что имеется Index Scan для того, чтобы что-то сделать и выбрать на массиве, мы заседаем, а он работает максимально плохим образом и все тормозит.

В этой ситуации запрос необходимо переписать, включив воображение. Я уже говорил, что Postgres умеет Hash Join, но не умеет делать хэширование массива. Давайте сконвертируем эту «простыню» таким образом, чтобы результат можно было с-Join'ить. В итоге получится то же самое, только оптимизатор выберет более разумный план.

Можно использовать такую конструкцию VALUES, которая нам все это превратит в ResultSet:

В этой ситуации на самом деле будет Index Scan и Hash Join. Будет произведено хэширование, и запрос довольно существенно ускорится.

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

И это достаточно легко переделать.

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

Есть еще одна очень существенная проблема – бывают такие запросы, с которыми нельзя ничего сделать. И первейший из этих запросов –count(*).

Например, есть интернет-сайт, у него – Главная страница, она высоконагруженная, на ней отображаются счетчики. Какую информацию пользователю несет число на счетчике, если это count из таблицы, которая часто обновляется? Число означает, что на тот момент, когда пользователь послал свой запрос, цифра была такая. Обычно пользователю не важно знать эту цифру с высокой точностью, достаточно более-менее приближенно, а чаще, вообще, хватает знания о том, растет число или нет. Если это какой-то финансовый баланс, то можно это делать, но обычно это делается очень редко и не выводится на Главную страницу сайта, чтобы при каждом обращении count гонялся по таблице.

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

Первый вариант решения этой проблемы – не использовать count'ы. Полезность его сомнительна, а ресурсов он занимает много. Второй момент – можно использовать приближенный count. Есть PG-каталог, из него можно по-select’ить, сколько строк в таблице было на момент последнего analyze'а, когда был произведен последний сбор статистики. Эта приблизительная цифра будет меняться достаточно часто, но при этом запрос по PG-каталогу не стоит практически ничего – это select одного value по условию названия таблицы. Если вы не хотите пускать интернет-юзера базы в PG-каталог, вам ничто не мешает написать хранимую процедуру, сказать ей «security definer» и дать права только на эту процедуру, и интернет-пользователь будет спокойно доставать эти данные без всяких проблем с security.

Следующий запрос-проблема – Join на 300 таблиц. Проблема состоит в том, что будет 300! вариантов, как этот Join сделать. Более того, если вам понадобилось написать Join на 300 таблиц, это значит, что у вас очень плохо с дизайном схемы, что-то очень не продумано, и надо много чего переделывать. В норме Join – это на две, на три таблицы. Иногда на пять, изредка на десять, но это крайние случаи. Когда Join'ов сотни, любой БД станет плохо.

Еще одна проблема – когда клиенту возвращается 1 000 000 строк. Кто долистывал до последней страницы в Google? Часто это бывает? Если вы видите, что онлайновый запрос, результат которого отображается на сайте, по каким-то причинам возвращает 1 млн. строк, задумайтесь – нет ли какой-то ошибки? Может понадобиться 10, 20 строк, может быть, 100, но 1 млн. строк человек не читает. Если у вас столько строк возвращается, это значит, что у вас либо какая-то выгрузка данных, которую можно сделать ночью, можно сделать с помощью дампа, можно еще каким-то способом, либо у вас просто неправильно написан запрос.

Например, вы сгенерили ORM'ом запрос для какай-то листалки, дальше, соответственно, вытаскиваете этот огромный массив, а используете из него реально только 10%. В этой ситуации вам нужно использовать limit и offset и каким-то другим способом определенным окном идти по этим данным и не тянуть их все на клиент, потому что 1 млн. строк – это всегда медленно, и, как правило, это не осмысленно и содержит какую-то логическую ошибку.

Контакты

hydrobiont

Этот доклад — расшифровка одного из лучших выступлений на конференции разработчиков высоконагруженных систем HighLoad++.

Также некоторые из этих материалов используются нами в обучающем онлайн-курсе по разработке высоконагруженных систем HighLoad.Guide — это цепочка специально подобранных писем, статей, материалов, видео. Уже сейчас в нашем учебнике более 30 уникальных материалов. Подключайтесь!

Ну и главная новость — мы начали подготовку весеннего фестиваля "Российские интернет-технологии", в который входит восемь конференций, включая HighLoad++ Junior.

Автор:

Источник

www.pvsm.ru

PostgreSQL : Документация: 9.6: 15.1. Как работают параллельно выполняемые запросы : Компания Postgres Professional

15.1. Как работают параллельно выполняемые запросы

Когда оптимизатор определяет, что параллельное выполнение будет наилучшей стратегией для конкретного запроса, он создаёт план запроса, включающий узел Gather (Сбор). Взгляните на простой пример:

EXPLAIN SELECT * FROM pgbench_accounts WHERE filler LIKE '%x%'; QUERY PLAN ------------------------------------------------------------------------------------- Gather (cost=1000.00..217018.43 rows=1 width=97) Workers Planned: 2 -> Parallel Seq Scan on pgbench_accounts (cost=0.00..216018.33 rows=1 width=97) Filter: (filler ~~ '%x%'::text) (4 rows)

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

Используя EXPLAIN, вы можете узнать количество исполнителей, выбранное планировщиком для данного запроса. Когда при выполнении запроса достигается узел Gather, процесс, обслуживающий сеанс пользователя, запрашивает фоновые рабочие процессы в этом количестве. Общее число фоновых рабочих процессов, которые могут существовать одновременно, ограничивается параметром max_worker_processes, так что вполне возможно, что параллельный запрос будет выполняться меньшим числом рабочих процессов, чем планировалось, либо вообще без дополнительных рабочих процессов. Оптимальность плана может зависеть от числа доступных рабочих процессов, так что их нехватка может повлечь значительное снижение производительности. Если это наблюдается часто, имеет смысл увеличить max_worker_processes, чтобы одновременно могло работать больше процессов, либо уменьшить max_parallel_workers_per_gather, чтобы планировщик ожидал их наличия в меньшем количестве.

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

postgrespro.ru