# Занятие 11: Статистика / Базовая статистика > Исходная страница в нативном Markdown: Статистика / Базовая статистика --- Индекс LLMS: [llms.txt](/ru/llms.txt) --- > [!NOTE] > Эта страница извлечена из официального PDF в нативный Markdown. Текст слайдов, код и комментарии сохранены в порядке чтения; исходные PDF и HTML остаются эталоном макета. > **HTML source:** [qpt_11_stats_basic.html](https://edu.postgrespro.ru/16/qpt-16/qpt_11_stats_basic.html) > **Archive:** [QPT-handouts-16.zip](https://edu.postgrespro.ru/16/QPT-handouts-16.zip) > **Страниц PDF:** 27 ## Page 1: Статистика / Базовая статистика {#source-page-001} ### Авторские права © Postgres Professional, 2019–2024 Авторы: Егор Рогов, Павел Лузанов, Павел Толмачев, Илья Баштанов Фото: Олег Бартунов (монастырь Пху и пик Бхрикути, Непал) ### Использование материалов курса Некоммерческое использование материалов курса (презентации, демонстрации) разрешается без ограничений. Коммерческое использование возможно только с письменного разрешения компании Postgres Professional. Запрещается внесение изменений в материалы курса. ### Обратная связь Отзывы, замечания и предложения направляйте по адресу: edu@postgrespro.ru ### Отказ от ответственности Компания Postgres Professional не несет никакой ответственности за любые повреждения и убытки, включая потерю дохода, нанесенные прямым или непрямым, специальным или случайным использованием материалов курса. Компания Postgres Professional не предоставляет каких-либо гарантий на материалы курса. Материалы курса предоставляются на основе принципа «как есть» и компания Postgres Professional не обязана предоставлять сопровождение, поддержку, обновления, расширения и изменения. ## Page 2: Темы {#source-page-002} - Базовая статистика - Наиболее частые значения и гистограммы - Статистика элементов составных значений - Использование статистики для оценки кардинальности и селективности - Частные и общие планы выполнения - Частичный индекс и индекс по выражению ## Page 3: Базовая статистика {#source-page-003} **Размер таблицы** - строки (pg\_class.reltuples) и страницы (pg\_class.relpages) - Собирается - операциями DDL - очисткой - анализом - Настройка - default\_statistics\_target = 100 ### Комментарии преподавателя Базовая статистика собирается на уровне всей таблицы и на уровне отдельных столбцов. К статистике таблицы относится информация о размере объекта (reltuples и relpages в таблице pg\_class). Поскольку такая статистика крайне важна, она обновляется некоторыми DDL-операциями (CREATE INDEX, CREATE TABLE AS SELECT) и уточняется при очистке и анализе. Кроме того, планировщик масштабирует количество строк в соответствии с отклонением реального размера файла данных от значения relpages. При анализе просматривается случайная выборка строк. Установлено, что размер выборки, обеспечивающий хорошую точность оценок, практически не зависит от размера таблицы. В качестве размера выборки используется ориентир статистики, заданный параметром default\_statistics\_target, умноженный на 300. При этом следует понимать, что статистика не должна быть абсолютно точной, чтобы планировщик мог выбрать приемлемый план; часто достаточно попадания в порядок. ## Page 4: Базовая статистика {#source-page-004} ![Source slide page 4: Базовая статистика](images/page-004.png){#source-figure-004 caption="Source slide page 4: Базовая статистика"} pg\_statistic (pg\_stats) ### Комментарии преподавателя Вся остальная статистика собирается отдельно для каждого столбца при анализе таблицы. Обычно этим занимается автоанализ (его настройка рассматривается в курсе DBA2). Статистика на уровне столбцов хранится в таблице pg\_statistic. Но смотреть проще в представление pg\_stats, которое показывает информацию в более удобном виде. Поле null\_frac содержит долю строк с неопределенными значениями в столбце (от 0 до 1). Поле n\_distinct хранит число уникальных значений в столбце. Если значение n\_distinct отрицательно, то модуль этого числа показывает долю уникальных значений. Например, −1 означает, что все значения уникальны (типичный случай для первичного ключа). INGLE-COLUMN ## Page 5: Число строк {#source-page-005} Начнем с оценки кардинальности в простом случае запроса без предикатов. ```sql => EXPLAIN SELECT * FROM flights; ``` ```text QUERY PLAN ---------------------------------------------------------------- Seq Scan on flights (cost=0.00..4772.67 rows=214867 width=63) (1 row) ``` Точное значение: ```sql => SELECT count(*) FROM flights; ``` ```text count -------- 214867 (1 row) ``` Оптимизатор получает значение из pg\_class: ```sql => SELECT reltuples, relpages FROM pg_class WHERE relname = 'flights'; ``` ```text reltuples | relpages -----------+---------- 214867 | 2624 (1 row) ``` Значение параметра, управляющего ориентиром статистики, по умолчанию равно 100: ```text => SHOW default_statistics_target; ``` ```text default_statistics_target --------------------------- 100 (1 row) ``` Поскольку при анализе таблицы учитывается 300 × default\_statistics\_target строк, то оценки для относительно крупных таблиц могут не быть абсолютно точными. ### Доля неопределенных значений Часть рейсов еще не отправились, поэтому время вылета для них не определено: ```sql => EXPLAIN SELECT * FROM flights WHERE actual_departure IS NULL; ``` ```text QUERY PLAN --------------------------------------------------------------- Seq Scan on flights (cost=0.00..4772.67 rows=16667 width=63) Filter: (actual_departure IS NULL) (2 rows) ``` Точное значение: ```sql => SELECT count(*) FROM flights WHERE actual_departure IS NULL; ``` ```text count ------- 16348 (1 row) ``` Оценка оптимизатора получена как общее число строк, умноженное на долю NULL-значений: ```sql => SELECT 214867 * null_frac FROM pg_stats WHERE tablename = 'flights' AND attname = 'actual_departure'; ``` ## Page 6: Число уникальных значений {#source-page-006} ```text ?column? -------------------- 16666.517337217927 (1 row) ``` Проверим количество моделей самолетов в таблице рейсов: ```sql => SELECT n_distinct FROM pg_stats WHERE tablename = 'flights' AND attname = 'aircraft_code'; ``` ```text n_distinct ------------ 8 (1 row) ``` Это соответствует действительности: ```sql => SELECT count(DISTINCT aircraft_code) FROM flights; ``` ```text count ------- 8 (1 row) ``` А в таблице самих самолетов? ```sql => SELECT n_distinct FROM pg_stats WHERE tablename = 'aircrafts_data' AND attname = 'aircraft_code'; ``` ```text n_distinct ------------ -1 (1 row) ``` Здесь значение -1 говорит о том, что каждое значение является уникальным. Это неудивительно, ведь aircraft\_code является первичным ключом в этой таблице. ### Кардинальность соединения Селективность соединения — доля строк от декартова произведения двух таблиц, которая остается после применения условия соединения. Поэтому для расчета кардинальности соединения оптимизатор оценивает кардинальность декартова произведения и умножает его на селективность условия соединения (и на селективность условий фильтров, если они есть). Рассмотрим пример: ```sql => EXPLAIN SELECT * FROM flights f JOIN aircrafts a ON a.aircraft_code = f.aircraft_code; ``` ```sql QUERY PLAN ------------------------------------------------------------------------------ Hash Join (cost=1.20..59857.41 rows=214867 width=103) Hash Cond: (f.aircraft_code = ml.aircraft_code) -> Seq Scan on flights f (cost=0.00..4772.67 rows=214867 width=63) -> Hash (cost=1.09..1.09 rows=9 width=72) -> Seq Scan on aircrafts_data ml (cost=0.00..1.09 rows=9 width=72) (5 rows) ``` Точное значение кардинальности: ```sql => SELECT count(*) FROM flights f JOIN aircrafts a ON a.aircraft_code = f.aircraft_code; ``` ```text count -------- 214867 (1 row) ``` ## Page 7: Page 7 {#source-page-007} Базовая формула для расчета селективности соединения (в предположении равномерного распределения) — минимальное из значений 1/nd1 и 1/nd2, где nd1 — число уникальных значений ключа соединения в первом наборе строк; nd2 — число уникальных значений ключа соединения во втором наборе строк. В данном случае получаем ровно то, что требуется: ```sql => SELECT round(214867 * 9 * least(1.0/8, 1.0/9)); ``` ```text round -------- 214867 (1 row) ``` ## Page 8: Наиболее частые значения {#source-page-008} ![Source slide page 8: Наиболее частые значения](images/page-008.png){#source-figure-008 caption="Source slide page 8: Наиболее частые значения"} ### Комментарии преподавателя Если бы данные всегда были распределены равномерно, то есть все значения встречались бы с одинаковой частотой, этой информации было бы почти достаточно (нужен еще минимум и максимум). Но в реальности неравномерные распределения встречаются очень часто. Поэтому собирается еще и следующая информация: ● массив наиболее частых значений — поле most\_common\_vals; ● массив частот этих значений — поле most\_common\_freqs. Частота из этих массивов непосредственно служит оценкой селективности для поиска конкретного значения. Все это прекрасно работает, пока число различных значений не очень велико. Максимальный размер каждого из массивов ограничен параметром default\_statistics\_target. Это значение можно переопределять на уровне отдельного столбца; в таком случае размер анализируемой выборки будет определяться по максимальному значению для таблицы. Тонкий момент представляют «большие» значения. Чтобы не увеличивать размер pg\_statistic и не нагружать планировщик бесполезной работой, значения, превышающие 1 Кбайт, исключаются из статистики и анализа. В самом деле, если в поле хранятся такие большие значения, скорее всего, они уникальны и не имеют шансов попасть в most\_common\_vals. ## Page 9: Наиболее частые значения {#source-page-009} Для эксперимента ограничим размер списка наиболее частых значений (который по умолчанию определяется параметром default\_statistics\_target) на уровне столбца: ```sql => ALTER TABLE flights ALTER COLUMN arrival_airport SET STATISTICS 10; ``` ```sql ALTER TABLE ``` ```text => ANALYZE flights; ``` ```text ANALYZE ``` Если значение попало в список наиболее частых, селективность можно узнать непосредственно из статистики. Пример (Шереметьево): ```sql => EXPLAIN SELECT * FROM flights WHERE arrival_airport = 'SVO'; ``` ```text QUERY PLAN --------------------------------------------------------------- Seq Scan on flights (cost=0.00..5309.84 rows=19381 width=63) Filter: (arrival_airport = 'SVO'::bpchar) (2 rows) ``` Точное значение: ```sql => SELECT count(*) FROM flights WHERE arrival_airport = 'SVO'; ``` ```text count ------- 19348 (1 row) ``` Вот как выглядит список наиболее частых значений и частота их встречаемости: ```sql => SELECT most_common_vals, most_common_freqs FROM pg_stats WHERE tablename = 'flights' AND attname = 'arrival_airport' \gx ``` ```text -[ RECORD 1 ]-----+----------------------------------------------------------------------------------- ----------------- most_common_vals | {DME,SVO,LED,VKO,OVB,KJA,SVX,ROV,PEE,BZK} most_common_freqs | {0.09736667,0.0902,0.0585,0.05293333,0.030833334,0.022166668,0.0206,0.018533334,0.01763333 4,0.0175} ``` Кардинальность вычисляется как число строк, умноженное на частоту значения: ```sql => SELECT 214867 * s.most_common_freqs[array_position((s.most_common_vals::text::text[]),'SVO')] FROM pg_stats s WHERE s.tablename = 'flights' AND s.attname = 'arrival_airport'; ``` ```text ?column? -------------------- 19381.003294982016 (1 row) ``` Список наиболее частых значений может использоваться и для оценки селективности неравенств. Для этого в most\_common\_vals надо найти все значения, удовлетворяющие неравенству, и просуммировать частоты соответствующих элементов из most\_common\_freqs. Если же указанного значения нет в списке наиболее частых, то селективность вычисляется исходя из предположения, что все данные (кроме наиболее частых) распределены равномерно. Например, в списке частых значений нет Владивостока. ```sql => EXPLAIN SELECT * FROM flights WHERE arrival_airport = 'VVO'; ``` ## Page 10: Page 10 {#source-page-010} ```text QUERY PLAN -------------------------------------------------------------- Seq Scan on flights (cost=0.00..5309.84 rows=1311 width=63) Filter: (arrival_airport = 'VVO'::bpchar) (2 rows) ``` Точное значение: ```sql => SELECT count(*) FROM flights WHERE arrival_airport = 'VVO'; ``` ```text count ------- 1188 (1 row) ``` Для получения оценки вычислим сумму частот наиболее частых значений: ```sql => SELECT sum(f) FROM pg_stats s, unnest(s.most_common_freqs) f WHERE s.tablename = 'flights' AND s.attname = 'arrival_airport'; ``` ```text sum ------------ 0.42626664 (1 row) ``` На менее частые значения приходятся оставшиеся строки. Поскольку мы исходим из предположения о равномерности распределения менее частых значений, селективность будет равна 1/nd, где nd — число уникальных значений: ```sql => SELECT n_distinct FROM pg_stats s WHERE s.tablename = 'flights' AND s.attname = 'arrival_airport'; ``` ```text n_distinct ------------ 104 (1 row) ``` Учитывая, что из этих значений 10 входят в список наиболее частых, а неопределенных значений нет, получаем следующую оценку: ```sql => SELECT 214867 * (1 - 0.42626664) / (104 - 10); ``` ```text ?column? ----------------------- 1311.4507006714893617 (1 row) ``` ## Page 11: Гистограмма {#source-page-011} ![Source slide page 11: Гистограмма](images/page-011.png){#source-figure-011 caption="Source slide page 11: Гистограмма"} ### Комментарии преподавателя Если число различных значений слишком велико, чтобы записать их в массив, на помощь приходит гистограмма. Гистограмма состоит из нескольких корзин, в которые помещаются значения. Количество корзин ограничено тем же параметром default\_statistics\_target. Ширина корзин выбирается так, чтобы в каждую попало примерно одинаковое число значений (на рисунке это выражается в одинаковой площади прямоугольников). При таком построении достаточно хранить только массив крайних значений каждой корзины — поле histogram\_bounds. Частота одной корзины равна 1/(число корзин). Оценить селективность условия поле < значение можно как N/(общее число корзин), где N — число корзин, лежащих слева от значения. Оценку можно улучшить, добавив часть корзины, в которую попадает само значение. Если же надо оценить селективность условия поле = значение, то гистограмма в этом не может помочь, и приходится довольствоваться предположением о равномерном распределении и брать в качестве оценки 1/n\_distinct. ## Page 12: Комбинация методов {#source-page-012} ![Source slide page 12: Комбинация методов](images/page-012.png){#source-figure-012 caption="Source slide page 12: Комбинация методов"} ### Комментарии преподавателя Но обычно два подхода объединяются: строится список наиболее частых значений, а все остальные значения покрываются гистограммой. При этом гистограмма строится так, что в ней не учитываются значения, попавшие в список. Это позволяет улучшить оценки. ## Page 13: Гистограмма {#source-page-013} При условиях «больше» и «меньше» для оценки будет использоваться список наиболее частых значений, или гистограмма, или оба способа вместе. Гистограмма строится так, чтобы не включать наиболее частые значения и NULL: ```sql => SELECT histogram_bounds FROM pg_stats s WHERE s.tablename = 'flights' AND s.attname = 'arrival_airport'; ``` ```text histogram_bounds ----------------------------------------------- {AAQ,CEK,GOJ,KGP,KZN,NBC,OMS,RGK,TJM,URJ,YKS} (1 row) ``` Число корзин гистограммы определяется параметром default\_statistics\_target, а границы выбираются так, чтобы в каждой корзине находилось примерно одинаковое количество значений. Рассмотрим пример: ```sql => EXPLAIN SELECT * FROM flights WHERE arrival_airport <= 'GOJ'; ``` ```text QUERY PLAN --------------------------------------------------------------- Seq Scan on flights (cost=0.00..5309.84 rows=49336 width=63) Filter: (arrival_airport <= 'GOJ'::bpchar) (2 rows) ``` Точное значение: ```sql => SELECT count(*) FROM flights WHERE arrival_airport <= 'GOJ'; ``` ```text count ------- 50520 (1 row) ``` Как получена оценка? Учтем частоту наиболее частых значений, попадающих в указанный интервал: ```sql => SELECT sum( s.most_common_freqs[array_position((s.most_common_vals::text::text[]),v)] ) FROM pg_stats s, unnest(s.most_common_vals::text::text[]) v WHERE s.tablename = 'flights' AND s.attname = 'arrival_airport' AND v <= 'GOJ'; ``` ```text sum ------------ 0.11486667 (1 row) ``` Указанный интервал занимает ровно 2 корзины гистограммы из 10, а неопределенных значений в данном столбце нет, получаем следующую оценку: ```sql => SELECT 214867 * ( 0.11486667 + (1 - 0.42626664) * (2.0 / 10.0) ); ``` ```text ?column? ------------------------------------ 49336.3299555140000000000000000000 (1 row) ``` В общем случае учитываются и не полностью занятые корзины (с помощью линейной аппроксимации). ### Кардинальность соединения В случае неравномерного распределения данных в ключах соединения базовая формула расчета селективности соединения (минимальное из значений 1/nd1 и 1/nd2) дает неправильный результат. Например, рейсы совершают разные модели самолетов с разной вместимостью, и для соединения рейсов с местами мы получили бы: ## Page 14: Page 14 {#source-page-014} ```sql => SELECT round(214867 * 1339 * least(1.0/8, 1.0/8)); ``` ```text round ---------- 35963364 (1 row) ``` При этом точное значение в два раза меньше: ```sql => SELECT count(*) FROM flights f JOIN seats s ON f.aircraft_code = s.aircraft_code; ``` ```text count ---------- 16518865 (1 row) ``` Однако планировщик умеет учитывать списки наиболее частых значений и гистограммы, и получает практически точную оценку: ```sql => EXPLAIN SELECT * FROM flights f JOIN seats s ON f.aircraft_code = s.aircraft_code; ``` ```sql QUERY PLAN ------------------------------------------------------------------------ Hash Join (cost=38.13..279462.54 rows=16614391 width=78) Hash Cond: (f.aircraft_code = s.aircraft_code) -> Seq Scan on flights f (cost=0.00..4772.67 rows=214867 width=63) -> Hash (cost=21.39..21.39 rows=1339 width=15) -> Seq Scan on seats s (cost=0.00..21.39 rows=1339 width=15) (5 rows) ``` К сожалению, ситуация ухудшается, когда соединяются несколько таблиц. Например, добавим в предыдущий запрос таблицу самолетов — это никак не повлияет на общее количество строк в выборке: ```sql => SELECT count(*) FROM flights f JOIN aircrafts a ON a.aircraft_code = f.aircraft_code JOIN seats s ON a.aircraft_code = s.aircraft_code; ``` ```text count ---------- 16518865 (1 row) ``` Однако теперь планировщик ошибается: ```sql => EXPLAIN SELECT * FROM flights f JOIN aircrafts a ON a.aircraft_code = f.aircraft_code JOIN seats s ON a.aircraft_code = s.aircraft_code; ``` ```sql QUERY PLAN ------------------------------------------------------------------------------ Hash Join (cost=39.33..8415391.09 rows=31967435 width=118) Hash Cond: (f.aircraft_code = ml.aircraft_code) -> Hash Join (cost=38.13..279462.54 rows=16614391 width=78) Hash Cond: (f.aircraft_code = s.aircraft_code) -> Seq Scan on flights f (cost=0.00..4772.67 rows=214867 width=63) -> Hash (cost=21.39..21.39 rows=1339 width=15) -> Seq Scan on seats s (cost=0.00..21.39 rows=1339 width=15) -> Hash (cost=1.09..1.09 rows=9 width=72) -> Seq Scan on aircrafts_data ml (cost=0.00..1.09 rows=9 width=72) (9 rows) ``` Дело в том, что, соединив первые две таблицы, планировщик не имеет детальной статистики о результирующем наборе строк. Во многих случаях именно это является основной причиной плохих оценок. ## Page 15: Дополнительные поля {#source-page-015} **Упорядоченность (использовать ли битовую карту?)** - pg\_stats.correlation - (1 — по возрастанию, 0 — хаотично, –1 — по убыванию) - Видимость (использовать ли сканирование только индекса?) - pg\_class.relallvisible - Средний размер значения в байтах (оценка памяти) - pg\_stats.avg\_width ### Комментарии преподавателя Сервер хранит еще несколько показателей статистики. В поле pg\_stats.correlation записывается показатель физической упорядоченности значений столбца. Если значения хранятся строго по возрастанию, показатель будет близок к единице; если по убыванию — к минус единице. Чем более хаотично расположены данные на диске, тем ближе значение показателя к нулю. Именно это поле использует оптимизатор, когда выбирает между сканированием битовой карты и обычным индексным сканированием. Поле pg\_class.relallvisible хранит количество страниц таблицы, которые содержат только актуальные версии строк (эта информация обновляется вместе с картой видимости). Если количество недостаточно велико, планировщик может отказаться от сканирования только индекса в пользу сканирования по битовой карте. В поле pg\_stats.avg\_width сохраняется средний размер значений в данном столбце в байтах для расчета необходимого для операции объема памяти. ## Page 16: Элементы составных полей {#source-page-016} **Наиболее частые элементы** - pg\_stats.most\_common\_elems - pg\_stats.most\_common\_elem\_freqs - Гистограмма количества элементов - pg\_stats.elem\_count\_histogram ### Комментарии преподавателя Для таких составных типов, как массивы или tsvector, в pg\_stats хранится распределение не только самих значений, но и их элементов: ● most\_common\_elems и most\_common\_elem\_freqs содержат наиболее частые элементы и их частоты; ● elem\_count\_histogram содержит гистограмму количества элементов в значении (например, в случае массива — гистограмму длин массивов). Это позволяет более точно планировать запросы с участием полей не в первой нормальной форме. В частности, эта информация важна для классов операторов индексного метода GIN, поскольку позволяет отделить частые значения (то есть встречающиеся во многих документах, условие с низкой селективностью) от редких (условие с высокой селективностью). ## Page 17: Элементы составных полей {#source-page-017} Для примера рассмотрим таблицу pg\_constraint — в ней хранятся ограничения целостности, определенные для таблиц. И в ней есть поле conkey с массивом номеров столбцов, образующих ограничение: ```sql => SELECT conname, conkey FROM pg_constraint WHERE conname LIKE 'boarding%'; ``` ```text conname | conkey -------------------------------------------+-------- boarding_passes_flight_id_boarding_no_key | {2,3} boarding_passes_flight_id_seat_no_key | {2,4} boarding_passes_pkey | {1,2} boarding_passes_ticket_no_fkey | {1,2} (4 rows) ``` Для этого поля можно получить наиболее частые значения и их частоты. Чтобы было удобнее просматривать статистику, соберем ее с уменьшенным значением ориентира: ```text => SET default_statistics_target = 7; ``` ```text SET ``` ```text => ANALYZE pg_constraint; ``` ```text ANALYZE ``` ```sql => SELECT most_common_vals, most_common_freqs FROM pg_stats WHERE tablename = 'pg_constraint' AND attname = 'conkey' \gx ``` ```text -[ RECORD 1 ]-----+--------------------------------------------------------------------------------- most_common_vals | {"{1}","{2}","{2,3}","{1,2}","{1,2,3}","{2,3,4}","{2,4}"} most_common_freqs | {0.4057971,0.10869565,0.10144927,0.06521739,0.036231883,0.028985508,0.028985508} ``` В данном случае значения в столбце — это массивы элементов. Такая статистика не позволит оценить кардинальность, например, для условия вхождения элемента в массив. Однако планировщик не ошибается в оценке: ```sql => SELECT count(*) FROM pg_constraint WHERE conkey @> ARRAY[2::smallint]; ``` ```text count ------- 65 (1 row) ``` ```sql => EXPLAIN SELECT * FROM pg_constraint WHERE conkey @> ARRAY[2::smallint]; ``` ```text QUERY PLAN ----------------------------------------------------------------- Seq Scan on pg_constraint (cost=0.00..5.72 rows=65 width=1073) Filter: (conkey @> '{2}'::smallint[]) (2 rows) ``` Для таких условий используется статистика по отдельным элементам: ```sql => SELECT most_common_elems, most_common_elem_freqs, elem_count_histogram FROM pg_stats WHERE tablename = 'pg_constraint' AND attname = 'conkey' \gx ``` ```text -[ RECORD 1 ]----------+------------------------------------------------------------------------------ -------------------------------------------------------------------------- most_common_elems | {1,2,3,4,5,6,7,8,9,10,20} most_common_elem_freqs | {0.5514706,0.4779412,0.30882353,0.11029412,0.036764707,0.022058824,0.022058824,0.022058824 ,0.022058824,0.007352941,0.007352941,0.007352941,0.5514706,0} elem_count_histogram | {1,1,1,1,2,2,4,1.5882353} ``` ## Page 18: Page 18 {#source-page-018} Для частот и гистограмм в конце массива содержится дополнительная информация (минимум, максимум, среднее), поэтому количество значений превышает установленный ориентир. Планировщик получает оценку, используя частоту элемента 2: ```sql => SELECT 0.4779412 * reltuples rows FROM pg_class WHERE relname = 'pg_constraint'; ``` ```text rows ------------ 65.9558856 (1 row) ``` ```text => RESET default_statistics_target; ``` ```text RESET ``` ```text => ANALYZE pg_constraint; ``` ```text ANALYZE ``` ## Page 19: Частные и общие планы {#source-page-019} **Частные планы** - может быть полезен при неравномерном распределении, - но запрос планируется заново при каждом вызове - Seq Scan on tasks - Index Scan on tasks - Index Cond: status = 'todo' - Filter: status = 'done' - строится - с учетом значений - параметров - Общий план - оптимален для равномерного распределения - кешируется в сеансе для подготовленных операторов - Index Scan on tasks - Index Cond: id = $1 - строится - без учета значений - параметров ### Комментарии преподавателя При простом протоколе запросов (вспомните тему «Планирование и выполнение») каждый запрос планируется заново с учетом значений параметров. Расширенный протокол позволяет подготавливать операторы, причем операторы могут иметь параметры. Подготовка всегда включает разбор и переписывание запроса, и дерево разбора сохраняется в локальной памяти обслуживающего процесса. При выполнении подготовленного оператора есть варианты. Запрос может, как обычно, планироваться каждый раз заново с учетом значений параметров. Такие планы называются частными (custom). Это имеет смысл при неравномерном распределении, ведь для разных значений оптимальные планы могут отличаться. Например, для высокоселективных условий может лучше работать индексный доступ, а если значение встречается часто — последовательный. Но запрос можно спланировать и без учета параметров. Это позволяет сохранить не только дерево разбора, но и сам план, и не тратить время на повторное планирование. Такой план называется общим (generic). Общий план отлично работает в случае равномерного распределения, когда селективность условия не зависит от конкретного значения. Но в случае неравномерного распределения общий план может хорошо работать для одних значений и плохо — для других. Разберемся, как планировщик решает, какой вариант использовать. ## Page 20: Частные и общие планы {#source-page-020} Подготовим запрос и создадим индекс: ```sql => PREPARE f(text) AS SELECT * FROM flights WHERE status = $1; ``` ```text PREPARE ``` ```sql => CREATE INDEX ON flights(status); ``` ```sql CREATE INDEX ``` Поиск отмененных рейсов будет использовать индекс, поскольку статистика говорит о том, что таких рейсов мало: ```sql => EXPLAIN EXECUTE f('Cancelled'); ``` ```text QUERY PLAN --------------------------------------------------------------------------------------- Index Scan using flights_status_idx on flights (cost=0.29..434.19 rows=415 width=63) Index Cond: ((status)::text = 'Cancelled'::text) (2 rows) ``` А поиск прибывших рейсов — нет, поскольку их много: ```sql => EXPLAIN EXECUTE f('Arrived'); ``` ```text QUERY PLAN ---------------------------------------------------------------- Seq Scan on flights (cost=0.00..5309.84 rows=198480 width=63) Filter: ((status)::text = 'Arrived'::text) (2 rows) ``` Такие планы называются частными, поскольку они построены с учетом конкретных значений параметров. Однако планировщик строит и общий план без учета конкретных значений параметров. Если в какой-то момент оказывается, что стоимость общего плана не превышает среднюю стоимость уже построенных ранее частных планов, планировщик начинает пользоваться общим планом, больше не выполняя планирование. Но первые пять раз используются частные планы, чтобы накопить статистику. Выполним оператор еще три раза: ```sql => EXPLAIN EXECUTE f('Arrived'); ``` ```text QUERY PLAN ---------------------------------------------------------------- Seq Scan on flights (cost=0.00..5309.84 rows=198480 width=63) Filter: ((status)::text = 'Arrived'::text) (2 rows) ``` ```sql => EXPLAIN EXECUTE f('Arrived'); ``` ```text QUERY PLAN ---------------------------------------------------------------- Seq Scan on flights (cost=0.00..5309.84 rows=198480 width=63) Filter: ((status)::text = 'Arrived'::text) (2 rows) ``` ```sql => EXPLAIN EXECUTE f('Arrived'); ``` ```text QUERY PLAN ---------------------------------------------------------------- Seq Scan on flights (cost=0.00..5309.84 rows=198480 width=63) Filter: ((status)::text = 'Arrived'::text) (2 rows) ``` Количество выполнений общего и частных планов можно посмотреть в представлении pg\_prepared\_statements: ```sql => SELECT name, generic_plans, custom_plans FROM pg_prepared_statements; ``` ## Page 21: Page 21 {#source-page-021} ```text name | generic_plans | custom_plans ------+---------------+-------------- f | 0 | 5 (1 row) ``` В следующий раз планировщик переключится на общий план. Вместо конкретного значения в плане будет указан номер параметра: ```sql => EXPLAIN EXECUTE f('Arrived'); ``` ```text QUERY PLAN --------------------------------------------------------------------------------------- Bitmap Heap Scan on flights (cost=401.83..3473.47 rows=35811 width=63) Recheck Cond: ((status)::text = $1) -> Bitmap Index Scan on flights_status_idx (cost=0.00..392.88 rows=35811 width=0) Index Cond: ((status)::text = $1) (4 rows) ``` ```sql => SELECT name, generic_plans, custom_plans FROM pg_prepared_statements; ``` ```text name | generic_plans | custom_plans ------+---------------+-------------- f | 1 | 5 (1 row) ``` Имея текст запроса с параметрами, представленными номерами (например, из журнала сообщений сервера), можно посмотреть общий план такого запроса следующим образом: ```sql => EXPLAIN (generic_plan) SELECT * FROM flights WHERE status = $1; ``` ```text QUERY PLAN --------------------------------------------------------------------------------------- Bitmap Heap Scan on flights (cost=401.83..3473.47 rows=35811 width=63) Recheck Cond: ((status)::text = $1) -> Bitmap Index Scan on flights_status_idx (cost=0.00..392.88 rows=35811 width=0) Index Cond: ((status)::text = $1) (4 rows) ``` Переход на общий план может быть нежелателен в случае неравномерного распределения значений. Параметр plan\_cache\_mode позволяет отключить использование частных планов (или наоборот, с самого начала использовать общий план): ```text => SHOW plan_cache_mode; ``` ```text plan_cache_mode ----------------- auto (1 row) ``` ```text => SET plan_cache_mode = 'force_custom_plan'; ``` ```text SET ``` ```sql => EXPLAIN EXECUTE f('Arrived'); ``` ```text QUERY PLAN ---------------------------------------------------------------- Seq Scan on flights (cost=0.00..5309.84 rows=198480 width=63) Filter: ((status)::text = 'Arrived'::text) (2 rows) ``` ```text => RESET plan_cache_mode; ``` ```text RESET ``` ### Частичный индекс Поиск уже прибывших рейсов будет выполняться последовательным сканированием таблицы flights, поэтому индексные записи с ключом Arrived никогда не используются. При этом они занимают место и требуют ресурсов для синхронизации с изменениями строк таблицы. Проверим размер полного индекса по полю status: ## Page 22: Page 22 {#source-page-022} ```sql => SELECT indexname, pg_size_pretty(pg_relation_size(indexname::text)) size FROM pg_indexes WHERE indexname LIKE 'flights_status%'; ``` ```text indexname | size --------------------+--------- flights_status_idx | 1472 kB (1 row) ``` Можно поместить в индекс только ссылки на строки, удовлетворяющие условиям с высокой селективностью. Заменим полный индекс на частичный: ```sql => DROP INDEX flights_status_idx; ``` ```sql DROP INDEX ``` ```sql => CREATE INDEX on flights(status) WHERE status IN ('Delayed', 'Departed', 'Cancelled'); ``` ```sql CREATE INDEX ``` Размер частичного индекса существенно меньше, чем полного: ```sql => SELECT indexname, pg_size_pretty(pg_relation_size(indexname::text)) size FROM pg_indexes WHERE indexname LIKE 'flights_status%'; ``` ```text indexname | size --------------------+------- flights_status_idx | 16 kB (1 row) ``` Когда это выгодно (для редких значений) будет использоваться частичный индекс: ```sql => EXPLAIN SELECT * FROM flights WHERE status = 'Cancelled'; ``` ```text QUERY PLAN --------------------------------------------------------------------------------------- Index Scan using flights_status_idx on flights (cost=0.15..438.04 rows=415 width=63) Index Cond: ((status)::text = 'Cancelled'::text) (2 rows) ``` ...а для частых значений — последовательное сканирование: ```sql => EXPLAIN SELECT * FROM flights WHERE status = 'Arrived'; ``` ```text QUERY PLAN ---------------------------------------------------------------- Seq Scan on flights (cost=0.00..5309.84 rows=198480 width=63) Filter: ((status)::text = 'Arrived'::text) (2 rows) ``` Таким образом можно сэкономить дисковое пространство и снизить затраты на сопровождение полного индекса. ### Индекс по выражению Если в условиях используются не столбцы, а, например, обращения к функциям, планировщик не учитывает множество значений. Например, рейсов, совершенных в январе, будет примерно 1/12 от общего количества: ```sql => SELECT count(*) FROM flights WHERE extract(month FROM scheduled_departure AT TIME ZONE 'Europe/Moscow') = 1; ``` ```text count ------- 16831 (1 row) ``` Однако планировщик не понимает смысла функции extract и использует фиксированную селективность 0,5%: ```sql => EXPLAIN SELECT * FROM flights WHERE extract(month FROM scheduled_departure AT TIME ZONE 'Europe/Moscow') = 1; ``` ## Page 23: Page 23 {#source-page-023} ```sql QUERY PLAN ------------------------------------------------------------------------------------------ --------------------- Gather (cost=1000.00..5943.27 rows=1074 width=63) Workers Planned: 1 -> Parallel Seq Scan on flights (cost=0.00..4835.87 rows=632 width=63) Filter: (EXTRACT(month FROM (scheduled_departure AT TIME ZONE 'Europe/Moscow'::text)) = '1'::numeric) (4 rows) ``` ```sql => SELECT 214867 * 0.005; ``` ```text ?column? ---------- 1074.335 (1 row) ``` Ситуацию можно исправить, построив индекс по выражению, так как для таких выражений собирается собственная статистика. ```sql => CREATE INDEX ON flights( extract(month FROM scheduled_departure AT TIME ZONE 'Europe/Moscow') ); ``` ```sql CREATE INDEX ``` ```text => ANALYZE flights; ``` ```text ANALYZE ``` Теперь оценка исправилась: ```sql => EXPLAIN SELECT * FROM flights WHERE extract(month FROM scheduled_departure AT TIME ZONE 'Europe/Moscow') = 1; ``` ```sql QUERY PLAN ------------------------------------------------------------------------------------------ ------------------------- Bitmap Heap Scan on flights (cost=317.98..3234.52 rows=16717 width=63) Recheck Cond: (EXTRACT(month FROM (scheduled_departure AT TIME ZONE 'Europe/Moscow'::text)) = '1'::numeric) -> Bitmap Index Scan on flights_extract_idx (cost=0.00..313.80 rows=16717 width=0) Index Cond: (EXTRACT(month FROM (scheduled_departure AT TIME ZONE 'Europe/Moscow'::text)) = '1'::numeric) (4 rows) ``` Статистика для индексов по выражению хранится вместе с базовой статистикой по столбцам таблиц: ```sql => SELECT n_distinct, most_common_vals, most_common_freqs FROM pg_stats WHERE tablename = 'flights_extract_idx' \gx ``` ```text -[ RECORD 1 ]-----+----------------------------------------------------------------------------------- ------------------------------------------- n_distinct | 12 most_common_vals | {8,9,7,10,5,1,12,4,3,6,11,2} most_common_freqs | {0.122433335,0.111033335,0.080233335,0.07966667,0.0795,0.0778,0.07703333,0.077,0.076233335 ,0.07536667,0.07266667,0.071033336} ``` Индекс можно построить только по детерминированному выражению, то есть для одинаковых значений параметров (столбцов) оно всегда должно выдавать одинаковый результат. Подробнее об этом рассказано в теме «Функции». ## Page 24: Итоги {#source-page-024} **Характеристики данных собираются в виде статистики** - Статистика нужна для оценки кардинальности - Кардинальность используется для оценки стоимости - Стоимость позволяет выбрать оптимальный план - Основа успеха — адекватная статистика и корректная кардинальность ## Page 25: Практика {#source-page-025} 1. Создайте индекс на таблице билетов (tickets) по имени пассажира (passenger\_name). 2. Какая статистика имеется для этой таблицы? 3. Объясните оценку кардинальности и выбор плана выполнения следующих запросов: а) выборка всех билетов, б) выборка билетов на имя ALEKSANDR IVANOV, в) выборка билетов на имя ANNA VASILEVA, г) выборка билета с идентификатором 0005432000284. ## Page 26: 1. Индекс {#source-page-026} ```sql => CREATE INDEX ON tickets(passenger_name); ``` ```sql CREATE INDEX ``` ### 2. Наличие статистики Некоторые основные значения: ```sql => SELECT reltuples, relpages FROM pg_class WHERE relname = 'tickets'; ``` ```text reltuples | relpages --------------+---------- 2.949857e+06 | 49415 (1 row) ``` ```sql => SELECT attname, null_frac nul, n_distinct, left(most_common_vals::text,20) mcv, cardinality(most_common_vals) mc, left(histogram_bounds::text,20) histogram, cardinality(histogram_bounds) hist, correlation FROM pg_stats WHERE tablename = 'tickets'; ``` ```text attname | nul | n_distinct | mcv | mc | histogram | hist | correlation ----------------+-----+-------------+----------------------+-----+----------------------+- -----+---------------- ticket_no | 0 | -1 | | | {0005432000293,00054 | 101 | 0.99999994 book_ref | 0 | -0.50253797 | | | {000204,0269B4,050E4 | 101 | 0.0029648095 passenger_id | 0 | -1 | | | {"0001 012839","0093 | 101 | 0.0072664083 passenger_name | 0 | 10367 | {"ALEKSANDR IVANOV", | 100 | {"ADELINA CHERNOVA", | 101 | -0.005419655 contact_data | 0 | -1 | | | {"{\"phone\": \"+700 | 101 | -0.00046799128 (5 rows) ``` Ни один столбец не содержит неопределенных значений. Уникальных номеров бронирования примерно в два раза меньше, чем строк в таблице (то есть на каждое бронирование в среднем приходится два билета). Имеется около 10000 разных имен. Все остальные столбцы содержат уникальные значения. Размеры массивов наиболее частых значений и гистограмм соответствуют значению параметра default\_statistics\_target (100). Для имен пассажиров есть наиболее частые значения. Для других столбцов они не имеют смысла, так как максимальное количество билетов (5) встречается в 194 бронированиях, а остальные столбцы уникальны. Гистограммы есть для всех столбцов, они нужны для оценки предикатов с условиями неравенства. Строки таблицы физически упорядочены по номеру билета. Данные в других столбцах расположены более или менее хаотично. ### 3. Планы запросов ```sql => EXPLAIN SELECT * FROM tickets; ``` ```text QUERY PLAN ------------------------------------------------------------------- Seq Scan on tickets (cost=0.00..78913.57 rows=2949857 width=104) (1 row) ``` Кардинальность равна числу строк в таблице; выбрано полное сканирование. ```sql => EXPLAIN SELECT * FROM tickets WHERE passenger_name = 'ALEKSANDR IVANOV'; ``` ## Page 27: Page 27 {#source-page-027} ```text QUERY PLAN ------------------------------------------------------------------------------------------ --- Bitmap Heap Scan on tickets (cost=73.20..17677.38 rows=6293 width=104) Recheck Cond: (passenger_name = 'ALEKSANDR IVANOV'::text) -> Bitmap Index Scan on tickets_passenger_name_idx (cost=0.00..71.63 rows=6293 width=0) Index Cond: (passenger_name = 'ALEKSANDR IVANOV'::text) (4 rows) ``` Селективность оценена по списку наиболее частых значений; выбрано сканирование по битовой карте. ```sql => EXPLAIN SELECT * FROM tickets WHERE passenger_name = 'ANNA VASILEVA'; ``` ```text QUERY PLAN ------------------------------------------------------------------------------------------ - Bitmap Heap Scan on tickets (cost=6.47..1004.20 rows=263 width=104) Recheck Cond: (passenger_name = 'ANNA VASILEVA'::text) -> Bitmap Index Scan on tickets_passenger_name_idx (cost=0.00..6.40 rows=263 width=0) Index Cond: (passenger_name = 'ANNA VASILEVA'::text) (4 rows) ``` Селективность оценена исходя из равномерного распределения; выбрано сканирование по битовой карте. ```sql => EXPLAIN SELECT * FROM tickets WHERE ticket_no = '0005432000284'; ``` ```text QUERY PLAN ------------------------------------------------------------------------------ Index Scan using tickets_pkey on tickets (cost=0.43..8.45 rows=1 width=104) Index Cond: (ticket_no = '0005432000284'::bpchar) (2 rows) ``` Кардинальность равна 1, так как значения этого столбца уникальны; выбрано индексное сканирование.