О чем молчит EXPLAIN, и как его разговорить / forpes.ru

Главная
О чем молчит EXPLAIN, и как его разговорить

О чем молчит EXPLAIN, и как его разговорить +37

26.11.2019 18:21

Kilor 69 5800 Источник

Классический вопрос, с которым разработчик приходит к своему DBA или владелец бизнеса — к консультанту по PostgreSQL, почти всегда звучит одинаково: «Почему запросы выполняются на базе так долго?»

Традиционный набор причин:

неэффективный алгоритм
когда вы решили сделать JOIN нескольких CTE по паре десятков тысяч записей
неактуальная статистика
если фактическое распределение данных в таблице уже сильно отличается от собранной ANALYZE'ом в последний раз
«затык» по ресурсам
и уже не хватает выделенных вычислительных мощностей CPU, постоянно прокачиваются гигабайты памяти или диск не успевает за всеми «хотелками» БД
блокировки от конкурирующих процессов

И если блокировки достаточно сложны в поимке и анализе, то для всего остального нам достаточно плана запроса, который можно получить с помощью оператора EXPLAIN (лучше, конечно, сразу EXPLAIN (ANALYZE, BUFFERS) ...) или модуля auto_explain.

Но, как сказано в той же документации,

«Понимание плана — это искусство, и чтобы овладеть им, нужен определённый опыт, …»

Но можно обойтись и без него, если воспользоваться подходящим инструментом!

Как обычно выглядит план запроса? Как-то вот так:

Index Scan using pg_class_relname_nsp_index on pg_class (actual time=0.049..0.050 rows=1 loops=1)
  Index Cond: (relname = $1)
  Filter: (oid = $0)
  Buffers: shared hit=4
  InitPlan 1 (returns $0,$1)
    ->  Limit (actual time=0.019..0.020 rows=1 loops=1)
          Buffers: shared hit=1
          ->  Seq Scan on pg_class pg_class_1 (actual time=0.015..0.015 rows=1 loops=1)
                Filter: (relkind = 'r'::"char")
                Rows Removed by Filter: 5
                Buffers: shared hit=1

или вот так:

"Append  (cost=868.60..878.95 rows=2 width=233) (actual time=0.024..0.144 rows=2 loops=1)"
"  Buffers: shared hit=3"
"  CTE cl"
"    ->  Seq Scan on pg_class  (cost=0.00..868.60 rows=9972 width=537) (actual time=0.016..0.042 rows=101 loops=1)"
"          Buffers: shared hit=3"
"  ->  Limit  (cost=0.00..0.10 rows=1 width=233) (actual time=0.023..0.024 rows=1 loops=1)"
"        Buffers: shared hit=1"
"        ->  CTE Scan on cl  (cost=0.00..997.20 rows=9972 width=233) (actual time=0.021..0.021 rows=1 loops=1)"
"              Buffers: shared hit=1"
"  ->  Limit  (cost=10.00..10.10 rows=1 width=233) (actual time=0.117..0.118 rows=1 loops=1)"
"        Buffers: shared hit=2"
"        ->  CTE Scan on cl cl_1  (cost=0.00..997.20 rows=9972 width=233) (actual time=0.001..0.104 rows=101 loops=1)"
"              Buffers: shared hit=2"
"Planning Time: 0.634 ms"
"Execution Time: 0.248 ms"

Но читать план текстом «с листа» — очень сложно и ненаглядно:

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

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

А, значит, нам нужен…

Инструмент

В нем мы постарались собрать все ключевые механики, которые помогают по плану и запросу понять, «кто виноват и что делать». Ну, и частью своего опыта поделиться с сообществом.
Встречайте и пользуйтесь — explain.tensor.ru

Наглядность планов

Легко ли понять план, когда он выглядит так?

Seq Scan on pg_class (actual time=0.009..1.304 rows=6609 loops=1)
  Buffers: shared hit=263
Planning Time: 0.108 ms
Execution Time: 1.800 ms

Не очень.

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

Но если план посложнее — на помощь придет piechart распределения времени по узлам:

Ну, а для самых сложных вариантов на помощь спешит диаграмма выполнения:

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

Структурные подсказки

Ну, а если вся структура плана и его больные места уже разложены и видны — почему бы не подсветить их разработчику, и не объяснить «русским языком»?

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

Построчный профайлер запроса

Теперь, если на анализируемый план наложить исходный запрос, то можно увидеть, сколько времени ушло на каждый отдельный оператор — примерно вот так:

… или даже так:

Подстановка параметров в запрос

Если вы «прицепили» к плану не только запрос, но и его параметры из DETAIL-строки лога, то сможете скопировать его дополнительно в одном из вариантов:

с подстановкой значений в запрос
для непосредственного выполнения на своей базе и дальнейшей профилировки
```
SELECT 'const', 'param'::text;
```
с подстановкой значений через PREPARE/EXECUTE
для эмуляции работы планировщика, когда параметрическая часть может быть проигнорирована — например, при работе на секционированных таблицах
```
DEALLOCATE ALL;
PREPARE q(text) AS SELECT 'const', $1::text;
EXECUTE q('param'::text);
```

Архив планов

Вставляйте, анализируйте, делитесь с коллегами! Планы останутся в архиве, и вы сможете вернуться к ним позднее: explain.tensor.ru/archive

Но если не хотите, чтобы ваш план увидели другие, не забудьте поставить галочку «не публиковать в архиве».

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

Комментарии (69)

Envek
26.11.2019 22:30
#20934780
Что-то у меня упорно не хочет работать explain.tensor.ru — везде отдаёт пустой файл браузеру (последние стабильные версии Firefox и Chromium под линуксом).

Попробовал несколько разных планов из личной коллекции «Как не надо писать SQL».

Например такой план:
```
 Finalize Aggregate  (cost=2684564053637.09..2684564053637.10 rows=1 width=8)
   ->  Gather  (cost=2684564053636.88..2684564053637.08 rows=2 width=8)
         Workers Planned: 2
         ->  Partial Aggregate  (cost=2684564052636.88..2684564052636.88 rows=1 width=8)
               ->  Parallel Index Only Scan using index_listings_on_source_id on listings
                (cost=0.56..2684564041656.88 rows=4391999 width=0)
                     Filter: (NOT (SubPlan 1))
                     SubPlan 1
                       ->  Materialize  (cost=0.00..607553.21 rows=1474540 width=4)
                             ->  Seq Scan on products  (cost=0.00..594420.51 rows=1474540 width=4)
                                   Filter: ((site_id IS NOT NULL) AND (item_id IS NOT NULL))
```
К такому запросу:
```
SELECT COUNT(*) FROM "listings"
WHERE "listings"."source_id" NOT IN (
  SELECT "products"."id" FROM "products"
  WHERE "products"."site_id" IS NOT NULL AND "products"."item_id" IS NOT NULL
)
```
1. Kilor Автор
  26.11.2019 23:12
  #20934930
  С запросом, но без analyze (то есть без реальных цифр исполнения) вставлять большого смысла нет — все равно только на cost опираться можно:
  
  Вставку наладим немного позже.
  1. Envek
    27.11.2019 12:11
    #20936562
    Запрос с cost в несколько триллионов попугаев с указанием analyze будет выполняться сутки, возможно, что и не одни :-D
    
    Kilor Автор
    27.11.2019 12:18
    #20936600
    Не так страшны cost-попугаи, как их малюет PG. :)
    Иногда погрешность между оценкой и реальностью составляет тысячи раз (а иногда и миллионы):
    например
    
    Xobotun
    27.11.2019 17:14
    #20938122
    Я сначала думал, может, баг сайта какой, explain закешировался в браузере. Но зашёл с другого компа – нет, мой план. :D
    
    Так и не смог понять, что надо скормить постгресу, чтобы он sequental scan превратил в index scan. Там поиск по таблице с ~1 млн фейковых записей, и фильтр по колонкам, которые в 99.5% истинны. То есть, возможно, там действительно укорять нечего.
    
    И да, я так понимаю, что cost-попугаи – условная стоимость чтения с диска и выполнения операций, а не предсказанное время. По крайней мере, так документация говорит.
    
    Kilor Автор
    27.11.2019 17:21
    #20938158
    Без запроса или output-атрибута рассуждать сложно, но раз там наверху Sort Key: st_distance..., то может и не надо сортировать вообще всех user, раз хотелось наиболее близких найти?
    
    Xobotun
    27.11.2019 20:07
    #20938892
    Это было риторическое утверждение, без попытки получить помощь. Хотел бы я её – пошёл бы на stackoverflow. :D
    
    Но если нечего делать и задачка интересна, то вот запрос. :)
    Типы данных, кмк, и так ясны, но у фильтров и юзеров есть TEXT[], если что.
    
    Там быстродействие не столь важно, результаты кешируются бекендом на пару минут для повторных запросов, но нет предела совершенству. И преждевременной оптимизации.
    
    Kilor Автор
    27.11.2019 21:05
    #20939066
    Чисто из спортивного интереса — а что за задача такая? То есть мы сортируем всех 1.1M юзеров по близости к заданному — а зачем такое может быть нужно?
    
    Xobotun
    28.11.2019 11:28
    #20940668
    Сервис знакомств типа Тиндера и прочих ему подобных.
    На самом деле я хочу сделать более защищённый алгоритм поиска расстояния между пользователями, но не хватает времени, всё на более приоритетные задачи уходит.
    
    Более защищённый в том плане, чтобы нельзя было вычислить настоящие координаты другого пользователя, сдвигая свои.
    Сейчас расстояние до другого пользователя показывается логарифмически "<1км", «5км», «10км», «25км», ..., но простым округлением. И если сдвигать свои координаты как раз на границе округления 1км-5км, то можно очень даже неплохо триангуляцией узнать приватные данные. Просто округлять в БД координаты WGS84 до четырёх знаков после запятой нехорошо, будет много пользователей с расстоянием в 0 км. Надо почитать на эту тему научных статей.
    
    А, и да, сортируются не все миллион пользователей, а тысяча-другая, которая осталась после фильтрации по всем условиям в WHERE. Хотя если фильтр будет крайне щадящий и пропустит всех пользователей, то, на удивление, всего 2.5 секунды выполняется. Я ожидал худшего. Хотя база на 60-гиговом ssd, external merge быстрый.
    
    Kilor Автор
    28.11.2019 11:58
    #20940872
    А выводится из этих 1-2 тысяч?
    Если фильтр малоселективный, то не эффективнее ли будет идти от сортировки по расстоянию, отфильтровывая неподходящих юзеров вложенным запросом?
    
    Xobotun
    28.11.2019 16:23
    #20942444
    Да, там есть ещё limit и offset, если пользователь стал просить других пользователей за пределами кеша, выводится ещё тысяча пользователей из результата.
    
    идти от сортировки по расстоянию, отфильтровывая неподходящих юзеров вложенным запросом
    А вот тут моих знаний sql и опыта уже явно не хватает, я просто не представляю, как и куда это сделать.
    
    ... WHERE id (SELECT /*здесь фильтры*/) ...
    
    Как-то так? Или через CTE?
    
    Kilor Автор
    28.11.2019 16:43
    #20942546
    Как-то примерно так:
    
    SELECT ... FROM users u WHERE apply_filter(u) ORDER BY st_distance(coord(u), coord($1)) LIMIT 100
    
    Ну и что-то типа KNN-gist в качестве индекса.
    
    Xobotun
    28.11.2019 16:45
    #20942552
    А, то есть вынести фильтр в отдельную sql-функцию?
    
    Хотел задействовать типы данных и индексы из постгиса, но не дошли руки. ':)
    
    Kilor Автор
    28.11.2019 16:48
    #20942570
    Не обязательно в функцию, можно под CASE «спрятать» — лишь бы не перебивало сортировку по distance индексом.
    
    Xobotun
    28.11.2019 16:50
    #20942580
    А как прятать под CASE?
    CASE WHEN TRUE THEN...? :)
    
    UPD: Это, похоже, не то, что имелось в виду.
    
    Kilor Автор
    28.11.2019 17:45
    #20942818
    ... WHERE CASE WHEN simple_cond(u) THEN complex_cond(u) END
    
    то есть что-то типа
    
    ... WHERE CASE WHEN u.sex = 'male' THEN EXISTS( SELECT NULL FROM children WHERE parent = u.id LIMIT 1 ) END
1. Kilor Автор
  27.11.2019 00:32
  #20935126
  Поправили вставку запроса в паре с "простым" explain (без analyze), смотрите:
  https://explain.tensor.ru/archive/explain/74aca500-908b-1308-16ec-2d63ece409ba:0:2019-11-27
1. ADR
  28.11.2019 15:54
  #20942268
  Мне кажеться та будет быстрее:
  
  SELECT COUNT(*) FROM "listings" INNER JOIN "products" ON products.id = listings.source_id WHERE "products"."site_id" IS NOT NULL AND "products"."item_id" IS NOT NULL
  
  то есть без sub query в where которая исполняеться для каждой строчки listing.
  1. Envek
    28.11.2019 16:39
    #20942518
    Ну я же говорил, что это из моей личной коллекции «Как не надо писать SQL» :-D
    
    В этом примере я рассказываю людям о том, что использовать WHERE NOT IN для логического отрицания, хоть и логически правильно, очень часто убийственно для производительности и запросы надо переписывать явно, используя IN и перенося отрицание внутрь подзапроса:
    
    SELECT COUNT(*) FROM "listings" WHERE "listings"."source_id" IN ( SELECT "products"."id" FROM "products" WHERE ("products"."site_id" IS NULL OR "products"."item_id" IS NULL) );
    
    И, хоть это немного и контринтуитивно, но такой вариант с подзапросом работает ощутимо быстрее вашего примера с JOIN (1 секунда против 8), хотя я сам думал, что ваш будет быстрее.
    
    Десятки вырвиглазных строк с EXPLAIN'ами
    EXPLAIN (ANALYZE,BUFFERS) SELECT COUNT(*) FROM "listings" WHERE "listings"."source_id" IN (SELECT "products"."id" FROM "products" WHERE ("products"."site_id" IS NULL OR "products"."item_id" IS NULL)); QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Finalize Aggregate (cost=390913.04..390913.05 rows=1 width=8) (actual time=912.009..912.010 rows=1 loops=1) Buffers: shared hit=1069212 -> Gather (cost=390912.82..390913.03 rows=2 width=8) (actual time=910.658..1022.559 rows=3 loops=1) Workers Planned: 2 Workers Launched: 2 Buffers: shared hit=1069212 -> Partial Aggregate (cost=389912.82..389912.83 rows=1 width=8) (actual time=901.824..901.825 rows=1 loops=3) Buffers: shared hit=1069212 -> Nested Loop (cost=8447.83..387299.92 rows=1045160 width=0) (actual time=160.246..871.369 rows=293153 loops=3) Buffers: shared hit=1069212 -> Parallel Bitmap Heap Scan on products (cost=8447.40..262462.63 rows=127617 width=4) (actual time=160.078..421.076 rows=52771 loops=3) Recheck Cond: ((site_id IS NULL) OR (item_id IS NULL)) Heap Blocks: exact=44070 Buffers: shared hit=129371 -> BitmapOr (cost=8447.40..8447.40 rows=319359 width=0) (actual time=120.626..120.626 rows=0 loops=1) Buffers: shared hit=17749 -> Bitmap Index Scan on index_products_on_site_id (cost=0.00..4836.83 rows=159680 width=0) (actual time=87.821..87.821 rows=158311 loops=1) Index Cond: (site_id IS NULL) Buffers: shared hit=8895 -> Bitmap Index Scan on index_products_on_item_id (cost=0.00..3457.43 rows=159680 width=0) (actual time=32.799..32.799 rows=158312 loops=1) Index Cond: (item_id IS NULL) Buffers: shared hit=8854 -> Index Only Scan using index_listings_on_source_id_and_site_id on listings (cost=0.43..0.88 rows=10 width=4) (actual time=0.005..0.007 rows=6 loops=158312) Index Cond: (source_id = products.id) Heap Fetches: 521 Buffers: shared hit=939841 Planning Time: 1.057 ms Execution Time: 1022.784 ms
    
    vs
    
    EXPLAIN (ANALYZE, BUFFERS) SELECT COUNT(*) FROM "listings" INNER JOIN "products" ON products.id = listings.source_id WHERE "products"."site_id" IS NOT NULL OR "products"."item_id" IS NOT NULL; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ---- Finalize Aggregate (cost=810010.97..810010.98 rows=1 width=8) (actual time=8011.359..8011.361 rows=1 loops=1) Buffers: shared hit=6341781 read=332216 dirtied=1, temp read=51798 written=51944 -> Gather (cost=810010.76..810010.97 rows=2 width=8) (actual time=8006.995..8194.180 rows=3 loops=1) Workers Planned: 2 Workers Launched: 2 Buffers: shared hit=6341781 read=332216 dirtied=1, temp read=51798 written=51944 -> Partial Aggregate (cost=809010.76..809010.77 rows=1 width=8) (actual time=7999.486..7999.486 rows=1 loops=3) Buffers: shared hit=6341781 read=332216 dirtied=1, temp read=51798 written=51944 -> Parallel Hash Join (cost=509013.67..792489.42 rows=6608535 width=0) (actual time=5442.368..7512.710 rows=5027767 loops=3) Hash Cond: (listings.source_id = products.id) Buffers: shared hit=6341781 read=332216 dirtied=1, temp read=51798 written=51944 -> Parallel Index Only Scan using index_listings_on_showcase_id_and_source_id on listings (cost=0.43..210880.60 rows=6653162 width=4) (actual time=0.100..2465.326 rows=5320919 loops =3) Heap Fetches: 17438 Buffers: shared hit=6186249 dirtied=1 -> Parallel Hash (cost=495773.71..495773.71 rows=806922 width=4) (actual time=1639.397..1639.397 rows=598917 loops=3) Buckets: 262144 Batches: 16 Memory Usage: 6496kB Buffers: shared hit=155434 read=332216, temp written=5044 -> Parallel Seq Scan on products (cost=0.00..495773.71 rows=806922 width=4) (actual time=0.060..1435.249 rows=598917 loops=3) Filter: ((site_id IS NOT NULL) OR (item_id IS NOT NULL)) Rows Removed by Filter: 52770 Buffers: shared hit=155434 read=332216 Planning Time: 0.930 ms Execution Time: 8196.618 ms
    
    ADR
    28.11.2019 17:16
    #20942684
    Ну я же говорил, что это из моей личной коллекции «Как не надо писать SQL» :-D
    Не увидел, виноват (главное читал же комент))
    
    И, хоть это немного и контринтуитивно, но такой вариант с подзапросом работает ощутимо быстрее вашего примера с JOIN (1 секунда против 8), хотя я сам думал, что ваш будет быстрее.
    Не знал о таком. Спасибо)
    
    ADR
    28.11.2019 18:14
    #20942950
    Хм. В варианте с inner join ошибка: должно быть не "IS NOT NULL", а "IS NULL".
    
    По експлейнах вижу Seq Scan on products в случаи с JOIN.
    Интересно что будет если все условия перенести в JOIN:
    
    SELECT COUNT(*) FROM "listings" INNER JOIN "products" ON products.id = listings.source_id and ( "products"."site_id" IS NULL OR "products"."item_id" IS NULL );
    
    Envek
    28.11.2019 19:34
    #20943284
    С условием да — mea culpa, mea maxima culpa — условие поправил и план похорошел и стал точно таким же, как и у подзапроса.
    
    Больше EXPLAIN богу EXPLAIN
    EXPLAIN (ANALYZE, BUFFERS) SELECT COUNT(*) FROM "listings" INNER JOIN "products" ON products.id = listings.source_id WHERE "products"."site_id" IS NULL OR "products"."item_id" IS NULL; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Finalize Aggregate (cost=390928.53..390928.54 rows=1 width=8) (actual time=921.498..921.499 rows=1 loops=1) Buffers: shared hit=1111266 -> Gather (cost=390928.31..390928.52 rows=2 width=8) (actual time=920.300..1049.853 rows=3 loops=1) Workers Planned: 2 Workers Launched: 2 Buffers: shared hit=1111266 -> Partial Aggregate (cost=389928.31..389928.32 rows=1 width=8) (actual time=914.771..914.772 rows=1 loops=3) Buffers: shared hit=1111266 -> Nested Loop (cost=8450.03..387315.41 rows=1045160 width=0) (actual time=119.589..883.410 rows=293190 loops=3) Buffers: shared hit=1111266 -> Parallel Bitmap Heap Scan on products (cost=8449.60..262464.83 rows=127617 width=4) (actual time=119.403..369.221 rows=52779 loops=3) Recheck Cond: ((site_id IS NULL) OR (item_id IS NULL)) Heap Blocks: exact=53046 Buffers: shared hit=129389 -> BitmapOr (cost=8449.60..8449.60 rows=319359 width=0) (actual time=85.178..85.178 rows=0 loops=1) Buffers: shared hit=17760 -> Bitmap Index Scan on index_products_on_site_id (cost=0.00..4837.93 rows=159680 width=0) (actual time=57.117..57.117 rows=158355 loops=1) Index Cond: (site_id IS NULL) Buffers: shared hit=8900 -> Bitmap Index Scan on index_products_on_item_id (cost=0.00..3458.53 rows=159680 width=0) (actual time=28.055..28.055 rows=158356 loops=1) Index Cond: (item_id IS NULL) Buffers: shared hit=8860 -> Index Only Scan using index_listings_on_source_id_and_site_id on listings (cost=0.43..0.88 rows=10 width=4) (actual time=0.006..0.008 rows=6 loops=158337) Index Cond: (source_id = products.id) Heap Fetches: 58308 Buffers: shared hit=981877 Planning Time: 0.908 ms Execution Time: 1050.009 ms

a0fs
26.11.2019 22:30
#20934786
+1
Вот бы эту штуку, хотя бы только с функционалом визуализации, да к себе на сервер. А то не всякий запрос можно так поразбирать, СИБ линчует…
1. Kilor Автор
  27.11.2019 00:43
  #20935146
  Вряд ли СИБ за несокрытие структуры полей-таблиц и времени исполнения переживать будет. :)
  А секретную информацию можно подрезать перед вставкой:
  Filter: (username = 'boss' AND userpass = 'secret')
1. strelkan
  27.11.2019 06:13
  #20935486
  в идеале бы в сборку bigsql
1. andrydl
  27.11.2019 08:18
  #20935644
  Галочка «не публиковать в архиве» и ваш план никто не увидит )

av_in
27.11.2019 09:42
#20935842
Тулза выглядит довольно старой, если ли какие-либо нюансы для новых версий postgres?
1. Kilor Автор
  27.11.2019 10:04
  #20935934
  Поддержка всех атрибутов и типов узлов v12.
  Пока не делали поддержку сторонних форков типа Greenplum и спецузлов в них вроде Gather Motion/Partition Selector/Dynamic Table Scan.
  А вам чего именно не хватает? Пишите — посмотрим, добавим.
  1. Cassiopeya
    27.11.2019 10:12
    #20935962
    а поддержка Greenplum планируется?
    
    Kilor Автор
    27.11.2019 10:17
    #20935976
    Если предоставите пачку планов/описание узлов — у нас Greenplum не используется, поэтому нет должного объема экспертизы пока.
    
    Cassiopeya
    27.11.2019 10:30
    #20936036
    Спасибо, подумаем об этом!

LPDem
27.11.2019 10:37
#20936050
Отличный инструмент, буду пользоваться!

vodopad
27.11.2019 10:57
#20936162
Какие альтернативы существуют для этого инструмента? Хотелось бы сравнить несколько и выбрать наиболее удобный.
1. Kilor Автор
  27.11.2019 11:07
  #20936212
  Мы сами отталкивались от проблем, которые есть тут:
  explain.depesz.com
  theartofpostgresql.com/explain-plan-visualizer
  Про часть из них я рассказывал на PGConf.

Kilor Автор
27.11.2019 11:05
#20936202
выше

Fragster
27.11.2019 14:05
#20937168
Кнопка развернуть свернуть все узлы работает как инверсия текущего состояния, а не как сворачивание и разворачивание всех узлов. Если некоторые узлы раскрыты, то они скрываются, зато показываются все остальные. это несколько неудобно. Пусть если хоть что-то свернуто, то разворачивает все, а потом все сворачивает.

Envek
27.11.2019 17:31
#20938200
Вот чего вам не хватает — так это флеймграфов, вот как эта тулза строит: https://github.com/mgartner/pg_flame (пример: https://mgartner.github.io/pg_flame/flamegraph.html )

Мне кажется, что это самый понятный способ визуализации происходящего. Не хотите добавить себе? Там в issues уже предлагали сделать подобное и даже js-реализацию находили: https://github.com/mgartner/pg_flame/issues/2
1. Kilor Автор
  27.11.2019 17:37
  #20938238
  Хотим, очень, но на реальных хоть немного сложных запросах получается редкостная каша, если узлы CTE/InitPlan/SubPlan используются по несколько раз.
  Поэтому пока остановились на piechart + граф.
1. Kilor Автор
  27.11.2019 18:51
  #20938596
  +1
  По сути, piechart (справа при наведении на шеврон выезжает) — и есть flamegraph, только свернутый в кольцо.
  1. Envek
    27.11.2019 20:17
    #20938918
    Кабы вы не рассказали про шеврон — ни в жизнь бы его не нашёл и не навёл бы на него. Круто, красота!
    
    Бажный он, немного, правда, на этом плане, но, надеюсь, почините как-нибудь.
    
    Kilor Автор
    27.11.2019 21:00
    #20939046
    В параллельных запросах Finalize-узлы отдают данные только с ведущего процесса, а остальные узлы — сумму по всем воркерам. Потому в результате отрицательные величины. :(
    В v12 есть хотя бы более детальная стата по каждому воркеру, а что делать с более ранними версиями пока не придумалось.
    
    Envek
    28.11.2019 16:43
    #20942548
    Ну, я надеюсь, что мы вскоре обновимся на 12-ю версию ;-)
    
    Kilor Автор
    28.11.2019 11:59
    #20940880
    Пофиксили piechart при отрицательных значениях узлов, спасибо.

oxff
28.11.2019 01:05
#20939580
Круто! Хотя и немного непривычно после explain.depesz.com, которым вы явно вдохновлялись.

1) Под Firefox мелкие косячки, потестируйте, пожалуйста. Например, размножающиеся диаграммы и т.п.
2) Рекомендации попадаются из разряда «пальцем в небо», вот прям совсем мимо.
3) Планируется ли английская версия интерфейса? Это ведь бета для хабратестеров?
1. Kilor Автор
  28.11.2019 09:24
  #20940168
  1. посмотрим, спасибо
  2. ссылку на пример покажите, плз, и почему она кажется неподходящей
  3. не в близком будущем
  1. oxff
    28.11.2019 17:27
    #20942730
    2) https://explain.tensor.ru/archive/explain/402e2dc375c7029ee792b3c4c7d6ec26:0:2019-11-28
    
    «Таблица сильно разрежена, рекомендуется произвести очистку с помощью VACUUM [FULL]». На самом деле этот план получен на staging машине, где только что развернули свежий дамп в единой транзакции, и не выполнили ни единого DML запроса. То есть таблица ну никак не может быть разреженной.
    
    3) Жалко, хотелось бы поделиться с коллегами. С другой стороны, правильно, пускай учат великий могучий, будет стимул :)
    
    Kilor Автор
    28.11.2019 17:53
    #20942860
    Это самый спорный кейс из нашей подборки. :)
    На самом деле, он означает, что записи лежат сильно далеко друг от друга — (rows = 1245, RRbF = 232), но при этом 1453 buffers. То есть вычитывается практически 1 страница данных/запись, а это достаточно много.
    И такое обычно бывает или если мы много-много раз апдейтили таблицу (тогда как раз VACUUM поможет), или если записи по ключу лежат «немного вначале, немного в конце» (тогда стоит посмотреть в сторону CLUSTER ON).
    
    oxff
    28.11.2019 18:17
    #20942976
    Эту табличку вскоре будут партиционировать среди прочих, по хэшу индексного ключа из обсуждаемого узла, а кластеризацию уже выполнили, это вы верно подметили :)
    
    oxff
    28.11.2019 21:26
    #20943644
    И ещё одно пожелание: кнопочка «развернуть/свернуть все узлы» инвертирует каждый узел в отдельности. Если часть из них уже развернута, то они сворачиваются и наоборот. Хорошо бы чтоб ее поведение зависело от глобального флага, и я не против кликнуть лишний раз, если флаг не отражает результат моих ручных действий. Что думаете?
    
    Kilor Автор
    28.11.2019 22:05
    #20943750
    Уже где-то тут в комментах проскакивало. Запилим на следующей неделе, думаю.

Kwisatz
28.11.2019 12:19
#20940982
когда вы решили сделать JOIN нескольких CTE по паре десятков тысяч записей

миллионов?

и уже не хватает выделенных вычислительных мощностей CPU, постоянно прокачиваются гигабайты памяти или диск не успевает за всеми «хотелками» БД

то вы скорее всего уже гугл, ну или очень жадный

Есть еще вот такая шикарнейшая штука, просто невероятно полезна при анализе больших запросов
1. Kilor Автор
  28.11.2019 13:06
  #20941248
  Ну как бы JOIN двух CTE по 10K записей — это 20мс на не самом плохом железе.
  PEV полезен, но принимает планы только в JSON, мы пока только в Text. Но подтянемся. :)
  1. Kwisatz
    28.11.2019 16:58
    #20942624
    NATURAL CROSS JOIN? Вот это стоило сразу уточнить. А то я все чаще встречаю людей, которые говорят что join это очень тяжелая операция.
    
    Envek
    28.11.2019 17:27
    #20942732
    А то я все чаще встречаю людей, которые говорят что join это очень тяжелая операция.
    Чем больше переписываешь проблемных запросов, тем чаще убеждаешься, что JOIN'ы порой приносят больше проблем, чем решают. Я в последнее время предпочитаю выносить условия по другим таблицам в подзапросы со ссылкой на внешний запрос — как правило это и работает быстрее и читается лучше. Например:
    
    SELECT posts.* FROM posts WHERE posts.user_id = 42 AND id IN ( SELECT post_id FROM comments WHERE posts.id = comments.post_id -- JOIN-условие и подсказка планировщику AND comments.something = … )
    
    Kwisatz
    28.11.2019 17:52
    #20942856
    Нет не лучше читается. Тем более, что выбирать все комменты по всем статьям пользователя не нужно никогда. А что собственно должно было у вас получиться в результате?
    
    Envek
    28.11.2019 19:43
    #20943314
    Тем более, что выбирать все комменты по всем статьям пользователя не нужно никогда.
    Посты и комменты здесь только, чтобы показать абстрактную связь один-ко-многим. На практике часто нужно выбирать записи, у которых есть определённные связанные записи. Просто вместе с JOIN'ом придётся лепить DISTINCT, чтобы избежать дублей (некоторые ORM так любят делать), а там прощай и производительность и читабельность.
    
    Нет не лучше читается.
    Дело вкуса, наверное
    
    Kwisatz
    28.11.2019 20:43
    #20943510
    Посты и комменты здесь только, чтобы показать абстрактную связь один-ко-многим. На практике часто нужно выбирать записи, у которых есть определённные связанные записи. Просто вместе с JOIN'ом придётся лепить DISTINCT, чтобы избежать дублей (некоторые ORM так любят делать), а там прощай и производительность и читабельность.
    
    Абстрактные примеры очень плохо отображают реальный мир. В реальном мире вы просто выведите список постов, плюсиков и левым джином докинете тему.
    
    ЗЫ только у меня на хабре скролл плющит нереально? На двух разных машинах в двух разных броузерах.
    
    Kilor Автор
    28.11.2019 18:03
    #20942902
    Если такой JOIN вместо Merge Join вырождается в Nested Loop как раз с множественным CTE Scan, то будет еще медленнее, чем в примере выше.
    Причем все время будет потрачено именно на CTE Scan-узле, а сам Join — дешевый, да. :)
    
    Kwisatz
    28.11.2019 20:45
    #20943516
    Вы заставляете базу прогнать 100 млн записей, тут удивляться нечему. Я бы даже сказал потрясающая производительность для такого объема. Но очень часто на фоне таких рассуждений народ начинает вытворять дичь в попытке уйти от левого джоина. В идеальном случае у вас во всех строках по джоину должна стоять единица, случаи когда это не так требуют отдельного рассмотрения.
    
    Kilor Автор
    28.11.2019 22:20
    #20943786
    В идеальном случае — можно без джойна. :)
    Примерно так — через hstore/json:
    
    WITH hs AS ( SELECT hstore( array_agg(id::text) , array_agg(T::text) ) FROM pktable T ) SELECT fk.* , (((TABLE hs) -> fk::text)::fktable).* FROM fktable
    
    Kwisatz
    28.11.2019 23:13
    #20943900
    ну серьезно чтоле? Это говнокод, в чистом его виде. И работает кстати медленно.
    
    Kilor Автор
    29.11.2019 08:51
    #20944622
    Кстати быстро.
    Достаточно представить, что в pktable 10M записей, и извлечение каждой отдельной из таблицы стоит существенно дороже, чем ее сериализация-десериализация.
    А после этого допустим, что в fktable 1K записей, но всего 10 _разных_ fk-ключей.
    
    Итого имеем:
    — 10 поисков по pktable, 10 сериализаций
    — 1K десериализаций
    vs (если будет Nested Loop)
    — 1K поисков по pktable
    
    Так что не бывает «говнокода в чистом виде», бывают вопросы эффективности применения метода. Хочу — применяю, не хочу — не применяю.
    
    Kwisatz
    29.11.2019 10:24
    #20944944
    До тех пор пока вы пишите свои собсттвенные пет проект — конечно. Как только вы начинаете работать по найму — бывает. Скорость выполнения это очень нужно, очень круто, сам люблю. Однако, дорабатывать надо, поддерживать надо, извольте нормализовать и писать очевидный код.
    
    Kilor Автор
    29.11.2019 10:48
    #20945054
    Бывают проекты, где разница скорости в несколько миллисекунд на каждом запросе определяет разницу в стоимости железа на десятки тысяч иностранных денег.
    И пока ФОТ разработчиков стоит кратно меньше, дешевле их научить таким подходам, чем вкладывать деньги в красиво написанный, но медленно работающий код.
    
    Kwisatz
    29.11.2019 10:55
    #20945092
    Поверьтте, я как раз такими вещами занимаюсь. У меня глаза на лоб лезут от того, какие вещи вытворяет народ с запросами. НО неподдерживаемый код нахрен никому ненужен, из-за него отдел разработки раздует так, что мало не покажется. И логика ваша не верна. Оборудование стоит _дешевле_. В том то и весь смысл.
    
    Kilor Автор
    29.11.2019 11:04
    #20945138
    Код не должен быть неподдерживаемым. Но он не обязан быть примитивным.
    Это все рассуждения из серии «разработчики — дураки, код с умножением для них слишком сложен, пусть пользуются только сложением».
    
    Kwisatz
    29.11.2019 12:57
    #20945754
    Нет не из этой серии. Я предлагаю вам не делать вставок на C поперек программы.
1. Envek
  28.11.2019 16:43
  #20942540
  PEV не развивается с момента создания в 2016-м, поэтому уже другие ребята сделали PEV2
  1. Kwisatz
    28.11.2019 17:02
    #20942642
    Хорошая штука, я как то не интересовался, меня устраивало) Спасибо.
    
    ЗЫ а под оракл таких штук нет кстате?

О чем молчит EXPLAIN, и как его разговорить +37

Инструмент

Наглядность планов

Структурные подсказки

Построчный профайлер запроса

Подстановка параметров в запрос

Архив планов

Комментарии (69)

Kilor Автор

Kilor Автор

Kilor Автор

Kilor Автор

Kilor Автор

Kilor Автор

Kilor Автор

Kilor Автор

Kilor Автор

Kilor Автор

Kilor Автор

Kilor Автор

Kilor Автор

Kilor Автор

Kilor Автор

Kilor Автор

Kilor Автор

Kilor Автор

Kilor Автор

Kilor Автор

Kilor Автор

Kilor Автор

Kilor Автор

Kilor Автор

Kilor Автор

Kilor Автор

Kilor Автор