Мы продолжаем готовиться к PG Day’16 и знакомить вас с интересными возможностями PostgreSQL.

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

В заключительном посте я постараюсь объяснить, почему Постгрес выбирает «Операцию X», а не «Операцию Y».



Возможно, вы слышали, что планировщик PostgreSQL выбирает операции, основываясь на статистике. Какой статистике?

Давайте представим самый простой сценарий из возможных:

SELECT * FROM table WHERE column = some_value;

Если у всех строк в таблиц одинаковое значение some_value, тогда применение к столбцу индекса (потенциально существующего) не имеет смысла.

С другой стороны, если значения в столбце уникальны (или почти уникальны), использование индекса – отличная идея.

Давайте посмотрим, что происходит:

create table test ( all_the_same int4, almost_unique int4 );
CREATE TABLE
 
insert into test ( all_the_same, almost_unique )
    select 123, random() * 1000000 from generate_series(1,100000);
INSERT 0 100000

Итак, у меня есть таблица на 100,000 строк, в которой столбец «all_the_same» всегда имеет одинаковые значения (123), а столбец almost_unique, как понятно из названия, почти уникален:

select count(*), count(distinct almost_unique) from test;
 count  | count 
--------+-------
 100000 | 95142
(1 row)

Теперь, чтобы сделать их равными, я создам два простых индекса:

create index i1 on test (all_the_same);
CREATE INDEX
 
create index i2 on test (almost_unique);
CREATE INDEX

Ок, тестовая конфигурация готова. А как насчет планов?

explain select * from test where all_the_same = 123;
                         QUERY PLAN                         
------------------------------------------------------------
 Seq Scan on test  (cost=0.00..1693.00 rows=100000 width=8)
   Filter: (all_the_same = 123)
(2 rows)
 
explain select * from test where almost_unique = 123;
                          QUERY PLAN                           
---------------------------------------------------------------
 Index Scan using i2 on test  (cost=0.29..8.31 rows=1 width=8)
   Index Cond: (almost_unique = 123)
(2 rows)

Как видите, Постгрес сделал мудрый выбор. Но здесь вызывает интерес оценочное значение «rows=». Откуда он знает, сколько строк может вернуть запрос?
Ответ лежит в команде ANALYZE или VACUUM ANALYZE.

Когда вы применяете к таблице «ANALYZE», Постгрес берет некий «случайный образец» (random sample) (через секунду расскажу об этом подробнее) и получает какие-то статистические данные. Что это за статистика, где она, и можем ли мы её увидеть? Конечно, можем:

select * from pg_statistic where starelid = 'test'::regclass;
-[ RECORD 1 ]-----------------------------------------------------------------------------
starelid    | 16882
staattnum   | 1
stainherit  | f
stanullfrac | 0
stawidth    | 4
stadistinct | 1
stakind1    | 1
stakind2    | 3
stakind3    | 0
stakind4    | 0
stakind5    | 0
staop1      | 96
staop2      | 97
staop3      | 0
staop4      | 0
staop5      | 0
stanumbers1 | {1}
stanumbers2 | {1}
stanumbers3 | [null]
stanumbers4 | [null]
stanumbers5 | [null]
stavalues1  | {123}
stavalues2  | [null]
stavalues3  | [null]
stavalues4  | [null]
stavalues5  | [null]
-[ RECORD 2 ]-----------------------------------------------------------------------------
starelid    | 16882
staattnum   | 2
stainherit  | f
stanullfrac | 0
stawidth    | 4
stadistinct | -0.92146
stakind1    | 1
stakind2    | 2
stakind3    | 3
stakind4    | 0
stakind5    | 0
staop1      | 96
staop2      | 97
staop3      | 97
staop4      | 0
staop5      | 0
stanumbers1 | {0.0001,0.0001,0.0001,0.0001,0.0001,0.0001,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05}
stanumbers2 | [null]
stanumbers3 | {-0.000468686}
stanumbers4 | [null]
stanumbers5 | [null]
stavalues1  | {21606,27889,120502,289914,417495,951355,283,1812,3774,6028,6229,10372,12234,13291,18309,18443,21758,22565,26634,28392,28413,31208,32890,36563,39277,40574,44527,49954,53344,53863,56492,56715,60856,62993,64294,65275,65355,68353,71194,74718,77205,82096,82783,84764,85301,87498,90990,94043,97304,98779,101181,103700,103889,106288,108562,110796,113154,117850,121578,122643,123874,126299,129236,129332,129512,134430,134980,136987,137368,138175,139001,141519,142934,143432,143707,144501,148633,152481,154327,157067,157799,162437,164072,164337,165942,167611,170319,171047,177383,184134,188702,189005,191786,192718,196330,197851,199457,202652,202689,205983}
stavalues2  | {2,10560,20266,31061,40804,50080,59234,69240,79094,89371,99470,109557,119578,130454,140809,152052,162656,173855,183914,194263,204593,214876,224596,233758,243246,253552,264145,273855,283780,294475,303972,314544,324929,335008,346169,356505,367395,376639,387302,397004,407093,416615,426646,436146,445701,455588,466463,475910,485228,495434,505425,515853,525374,534824,545387,554794,563591,573721,584021,593368,602935,613238,623317,633947,643431,653397,664177,673976,684042,694791,703922,714113,724602,735848,745596,754477,764171,772535,781924,791652,801703,812487,822196,831618,841665,850722,861532,872067,881570,891654,901595,910975,921698,931785,940716,950623,960551,970261,979855,989540,999993}
stavalues3  | [null]
stavalues4  | [null]
stavalues5  | [null]

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

К счастью, существует view для этой таблицы, который содержит те же самые данные в более «читабельном» представлении:

select * from pg_stats where tablename = 'test';
-[ RECORD 1 ]----------+------------------------------------------------------------------
schemaname             | public
tablename              | test
attname                | all_the_same
inherited              | f
null_frac              | 0
avg_width              | 4
n_distinct             | 1
most_common_vals       | {123}
most_common_freqs      | {1}
histogram_bounds       | [null]
correlation            | 1
most_common_elems      | [null]
most_common_elem_freqs | [null]
elem_count_histogram   | [null]
-[ RECORD 2 ]----------+------------------------------------------------------------------
schemaname             | public
tablename              | test
attname                | almost_unique
inherited              | f
null_frac              | 0
avg_width              | 4
n_distinct             | -0.92146
most_common_vals       | {21606,27889,120502,289914,417495,951355,283,1812,3774,6028,6229,10372,12234,13291,18309,18443,21758,22565,26634,28392,28413,31208,32890,36563,39277,40574,44527,49954,53344,53863,56492,56715,60856,62993,64294,65275,65355,68353,71194,74718,77205,82096,82783,84764,85301,87498,90990,94043,97304,98779,101181,103700,103889,106288,108562,110796,113154,117850,121578,122643,123874,126299,129236,129332,129512,134430,134980,136987,137368,138175,139001,141519,142934,143432,143707,144501,148633,152481,154327,157067,157799,162437,164072,164337,165942,167611,170319,171047,177383,184134,188702,189005,191786,192718,196330,197851,199457,202652,202689,205983}
most_common_freqs      | {0.0001,0.0001,0.0001,0.0001,0.0001,0.0001,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05}
histogram_bounds       | {2,10560,20266,31061,40804,50080,59234,69240,79094,89371,99470,109557,119578,130454,140809,152052,162656,173855,183914,194263,204593,214876,224596,233758,243246,253552,264145,273855,283780,294475,303972,314544,324929,335008,346169,356505,367395,376639,387302,397004,407093,416615,426646,436146,445701,455588,466463,475910,485228,495434,505425,515853,525374,534824,545387,554794,563591,573721,584021,593368,602935,613238,623317,633947,643431,653397,664177,673976,684042,694791,703922,714113,724602,735848,745596,754477,764171,772535,781924,791652,801703,812487,822196,831618,841665,850722,861532,872067,881570,891654,901595,910975,921698,931785,940716,950623,960551,970261,979855,989540,999993}
correlation            | -0.000468686
most_common_elems      | [null]
most_common_elem_freqs | [null]
elem_count_histogram   | [null]

Отлично. Так какие же знания мы можем отсюда почерпнуть?

Столбцы schemaname, tablename и attname кажутся очевидными. Inherited просто сообщает, содержат ли значения этой таблицы значения из любых таблиц, которые унаследовали этот столбец.

Так что, если бы я создал таблицу:

create table z () inherits (test);

А потом добавил в эту таблицу z какие-то данные, то статистика таблицы test показала бы «inherited = true».

Остальные столбцы означают следующее:

• null_frac — сколько строк имеют значение null в данном столбце. Это доля, поэтому значение будет от 0 до 1.
• avg_width — средняя ширина (прим. пер.: размер) данных в этом столбце. Это не очень интересно, если ширина постоянна (как у int4 в этом примере), а вот в случае с любыми типами данных с переменной шириной (как у text/varchar/numeric) это может пригодиться.
• n_distinct — очень интересная величина. Если она положительная (1+), то это будет просто ориентировочное число (не доля!) различных значений, как мы видим в случае со столбцом all_the_same, где n_distinct справедливо равна 1. А если она отрицательная, то смысл меняется: n_distinct показывает, какая доля строк имеет уникальное значение. Поэтому, в случае с almost_unique статистика полагает, что 92.146% строк имеют уникальное значение (что немного меньше 95.142%, которые я показывал ранее). Значения могут быть неверны из-за той штуки с «случайным образцом», которую я уже упоминал и чуть позже объясню подробно.
• most_common_vals — массив наиболее распространенных значений в этой таблице.
• most_common_freqs — как часто встречаются значения из most_common_vals — это тоже доля, так что максимальное значение — 1 (но это будет означать, что у нас всего одно значение в most_common_vals). Здесь, в almost_unique, мы видим, что Постгрес «думает», что значения 21606, 27889, 120502, 289914, 417495, 951355 встречаются чаще всего, но это не так. Опять же, во всём виноват эффект «случайного образца».
• histogram_bounds — массив значений, который делит (или должен делить — снова всё упирается в «случайный образец») весь набор данных на группы с одинаковым количеством строк. То есть количество строк almost_unique между 2 и 10560 такое же (более-менее), как и количество строк almost_unique между 931785 и 940716.
• correlation — это очень интересная статистика, она показывает, есть ли корреляция между физической сортировкой строк на диске и значениями. Эта величина может меняться от -1 до 1, и чем ближе она к -1/1, тем больше корреляция. Например, после запуска «CLUSTER test using i2», который пересортировывает таблицу в порядке almost_unique, я получил корреляцию 0.919358 — гораздо лучше по сравнению с предыдущим значением -0.000468686.
  most_common_elems, most_common_elem_freqs и elem_count_histogram такие же, как most_common_vals, most_common_freqs и histogram_bounds, но для нескалярных типов данных (то есть, arrays, tsvectors и alike).

Основываясь на этих данных, PostgreSQL может приблизительно оценить, сколько строк будет возвращено любой выбранной частью запроса, и, исходя из этой информации, решить, что лучше использовать: seq scan, index scan или bitmap index scan. А при объединении — какая операция должна быть быстрее: Hash Join, Merge Join или, быть может, Nested Loop.

Если вы внимательно изучили представленные выше данные, то могли задаться вопросом: это достаточно обширный набор выходных данных, в массивах most_common_vals/most_common_freqs/histogram_bounds содержится много значений. Почему же их так много?

Причина проста — всё дело в настройках. В postgresql.conf вы можете найти переменную default_statistics_target. Эта переменная говорит Постгресу, сколько значений хранить в этих массивах. В моём случае (по умолчанию) это число равно 100. Но вы можете легко изменить его. Внести изменение в postgresql.conf, или даже для каждого отдельно взятого столбца вот таким образом:

alter table test alter column almost_unique set statistics 5;

После применения ALTER (и ANALYZE) данные в pg_stats существенно укорачиваются:

select * from pg_stats where tablename = 'test' and not inherited and attname = 'almost_unique';
-[ RECORD 1 ]----------+---------------------------------------------------------
schemaname             | public
tablename              | test
attname                | almost_unique
inherited              | f
null_frac              | 0
avg_width              | 4
n_distinct             | -0.92112
most_common_vals       | {114832,3185,3774,6642,11984}
most_common_freqs      | {0.0001,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05}
histogram_bounds       | {2,199470,401018,596414,798994,999964}
correlation            | 1
most_common_elems      | [null]
most_common_elem_freqs | [null]
elem_count_histogram   | [null]

Изменение statistic target также имеет ещё один эффект.

Давайте я вам покажу. Для начала я откачу изменения в подсчете статистики, которые я внёс с помощью ALTER TABLE:

alter table test alter column almost_unique set statistics -1;

А теперь сделаем следующее:

$ analyze verbose test;
INFO:  analyzing "public.test"
INFO:  "test": scanned 443 of 443 pages, containing 100000 live rows and 0 dead rows; 30000 rows in sample, 100000 estimated total rows
ANALYZE
 
$ alter table test alter column almost_unique set statistics 10;
ALTER TABLE
 
$ alter table test alter column all_the_same set statistics 10;
ALTER TABLE
 
$ analyze verbose test;
INFO:  analyzing "public.test"
INFO:  "test": scanned 443 of 443 pages, containing 100000 live rows and 0 dead rows; 3000 rows in sample, 100000 estimated total rows
ANALYZE

Заметьте, что второй analyze протестировал всего 3000 строк, а не 30000, как первый.

Это и есть «случайный образец».

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

Поэтому Постгрес поступает умнее.

Во-первых, он читает случайную часть страниц в таблице (напоминаю: каждая страница — это 8кБ данных). Сколько именно? 300 * statistics_target.

Это значит, что в моём случае с default_statistics_target = 100 он прочитает 30000 страниц (в моей таблице столько нет, поэтому Постгрес прочитает их все).

Из этих страниц ANALYZE берет только информацию о живых и мёртвых строках. Затем он получает данные о случайном образце строк — снова 300 * statistics target — и считает статистику по столбцу, основываясь на этих данных.

В моём случае в таблице было 100,000 строк, но с default_statistics_target = 100 только треть была проанализирована. А, с учетом значения statistics target, количество проанализированных строк ещё меньше — всего 3000.

Вы могли бы сказать: ОК, но в таком случае эта статистика неточная. Может так случиться, что какое-нибудь супер-распространенное значение не попалось ни в одной из просканированных строк. Конечно, вы правы. Это возможно. Хотя и не слишком вероятно. Вы берете случайную часть данных. Шансы, что вы получите x% таблицы, в которой нет ни одной строки с каким-то значением, которое присутствует во всех остальных строках, ничтожно малы.

Это также значит, что в некоторых случаях запуск analyze будет «ломать» ваши запросы. Например, вы получите статистику по другим страницам, и выйдет так, что некоторые значения окажутся пропущены (или наоборот — вы получите в most_common_vals не такие уж распространенные значения, просто так получилось, что Постгрес выбрал подходящие страницы/строки, чтобы их увидеть). И на основании такой статистики Pg будет генерировать неоптимальные планы.

Если вы столкнётесь с такой ситуацией, решить её достаточно просто — увеличьте statistics target. Это заставит analyze работать усерднее и сканировать больше строк, поэтому шансы, что подобное повторится, станут ещё меньше.

Но в установке больших значений statistics target есть определенные недостатки. Во-первых, ANALYZE приходится больше работать, но это вопрос эксплуатации, так что он нас не слишком волнует (обычно). Основная же проблема заключается в том, что, чем больше данных в pg_statistic, тем больше данных должно приниматься во внимание планировщиком Pg. Поэтому, как бы ни было заманчиво установить default_statistics_target на максимум в 10,000, в реальности я не встречал баз данных, в которых это значение было бы таким высоким.

Текущие 100 по умолчанию установлены, начиная с версии 8.4. В предыдущих версиях значение по-умолчанию было 10, и на irc часто встречались советы его увеличить. Теперь со значением 100 всё более-менее настроено.

Последнее, о чем мне придётся рассказать, хоть и не очень хочется, — настройки, которые заставляют планировщик Постгреса использовать разные операции.

Во-первых, объясню, почему мне не хочется об этом говорить: я точно знаю, что этим можно легко злоупотребить. Так что запомните: эти настройки нужны для поиска проблем, а не для их решения. Приложение, которое будет использовать их в рабочем режиме, можно, как минимум, заподозрить в том, что оно сломано. И да, я знаю, что иногда приходится так делать. Но это «иногда» случается крайне редко.

Теперь, когда я вас предупредил, давайте посмотрим, что можно сделать.

В postgresql.conf у вас есть несколько настроек:

enable_bitmapscan = on
enable_hashagg = on
enable_hashjoin = on
enable_indexscan = on
enable_indexonlyscan = on
enable_material = on
enable_mergejoin = on
enable_nestloop = on
enable_seqscan = on
enable_sort = on
enable_tidscan = on

Эти настройки нужны для отключения выбранных операций.

Например, переключение enable_seqscan на false (это можно сделать с помощью команды SET в сессии SQL, вам не нужно изменять postgresql.conf) приведёт к тому, что планировщик будет использовать всё, что только можно, дабы избежать последовательного сканирования.

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

Приведем пример. В отношении нашей тестовой таблицы мы знаем, что поиск с помощью «all_the_same = 123» будет использовать последовательное сканирование, потому что оно не требует больших затрат:

explain select * from test where all_the_same = 123;
                         QUERY PLAN                         
------------------------------------------------------------
 Seq Scan on test  (cost=0.00..1693.00 rows=100000 width=8)
   Filter: (all_the_same = 123)
(2 rows)

Но если мы отключим seq scan:

set enable_seqscan = false;
SET
explain select * from test where all_the_same = 123;
                              QUERY PLAN                               
-----------------------------------------------------------------------
 Index Scan using i1 on test  (cost=0.29..3300.29 rows=100000 width=8)
   Index Cond: (all_the_same = 123)
(2 rows)

Мы видим, что оценочная стоимость получения тех же данных с помощью index scan ~ в два раза выше (3300.29 против 1693).

Если я удалю i1 индекс:

drop index i1;
DROP INDEX
set enable_seqscan = false;
SET
explain select * from test where all_the_same = 123;
                                 QUERY PLAN                                  
-----------------------------------------------------------------------------
 Seq Scan on test  (cost=10000000000.00..10000001693.00 rows=100000 width=8)
   Filter: (all_the_same = 123)
(2 rows)

И мы видим, что, когда других возможностей, кроме последовательного сканирования, нет (интересно, что Постгрес не выбрал провести index scan по i2, хотя у этого индекса есть указатели на все строки в таблице), затраты взлетели до 10,000,000,000 — именно это enable_* = false и делает.

Думаю, на этом всё. Если вы прочитали всю серию, теперь вам должно хватать знаний, чтобы понимать, что происходит и, что важнее, — почему.