Статистика планировщика

Добавьте в таблицу fatab столбец типа float.

postgres@monman_db=# ALTER TABLE fatab ADD nme float;

ALTER TABLE

Проверьте структуру таблицы.

postgres@monman_db=# \d fatab

Table "public.fatab"
Column  |       Type       | Collation | Nullable |              Default
--------+------------------+-----------+----------+-----------------------------------
id      | integer          |           | not null | nextval('fatab_id_seq'::regclass)
dte     | date             |           |          |
nme     | double precision |           |          |
Indexes:
        "fatab_pkey" PRIMARY KEY, btree (id)

В таблицу добавлен столбец nme типа double precision (внутреннее представление float).

Отключите автоочистку для таблицы fatab.

postgres@monman_db=# ALTER TABLE fatab SET (autovacuum_enabled = off);

ALTER TABLE

Заполните добавленное поле во всех строках таблицы fatab случайными значениями, генерируемыми функцией random() — эти значения находятся в диапазоне от 0 до 1.
```
postgres@monman_db=# UPDATE fatab SET nme = random();
```
```
UPDATE 100000
```
Обновите приблизительно 1% строк в таблице, увеличив значение в столбце nme в 10 раз.
```
postgres@monman_db=# UPDATE fatab SET nme = nme*10 WHERE random() < 0.01;
```
Разбор команды
- UPDATE fatab SET nme = nme*10 — умножение значения столбца nme на 10;
- WHERE random() < 0.01 — условие, отбирающее примерно 1 % строк: функция random() возвращает число от 0 до 1, и менее 0,01 попадает около 1 % значений.
```
UPDATE 1010
```

Добавьте индекс к таблице по полю nme.

postgres@monman_db=# CREATE INDEX ON fatab (nme);

CREATE INDEX

Проверьте структуру таблицы.

postgres@monman_db=# \d fatab

                               Table "public.fatab"
 Column |       Type       | Collation | Nullable |              Default
--------+------------------+-----------+----------+-----------------------------------
 id     | integer          |           | not null | nextval('fatab_id_seq'::regclass)
 dte    | date             |           |          |
 nme    | double precision |           |          |
 Indexes:
         "fatab_pkey" PRIMARY KEY, btree (id)
         "fatab_nme_idx" btree (nme)

Индекс fatab_nme_idx создан по столбцу nme и теперь доступен для планировщика запросов.

Выгрузите таблицу в отсортированном виде во внешний файл и очистите ее. Затем загрузите данные обратно.

Скопируйте данные из таблицы в файл, отсортировав по столбцу nme.
```
postgres@monman_db=# COPY (SELECT * FROM fatab ORDER BY nme) TO '/tmp/fatab.sql';
```
```
COPY 100000
```
Очистите таблицу и загрузите данные из файла.
```
postgres@monman_db=# TRUNCATE fatab;
```
```
TRUNCATE TABLE
```
```
postgres@monman_db=# COPY fatab FROM '/tmp/fatab.sql';
```
```
COPY 100000
```
Данные выгружены и загружены обратно — теперь строки в таблице физически отсортированы по столбцу nme, но статистика планировщика об этом еще не знает.
Получите план выполнения запроса, возвращающего строки, где значение поля nme > 1.
```
postgres@monman_db=# EXPLAIN SELECT * FROM fatab WHERE nme > 1;
```
```
QUERY PLAN
----------------------------------------------------------------------------------
Bitmap Heap Scan on fatab  (cost=674.60..1632.62 rows=33362 width=16)
  Recheck Cond: (nme > '1'::double precision)
    ->  Bitmap Index Scan on fatab_nme_idx  (cost=0.00..666.26 rows=33362 width=0)
         Index Cond: (nme > '1'::double precision)
(4 rows)
```
В плане видно значительную ошибку оценки количества возвращаемых строк — планировщик предсказал 33362 строки вместо реальных 10000. Также выбран метод сканирования по индексу с битовой картой, который необходим при хаотичном расположении данных. На самом же деле строки после загрузки упорядочены по столбцу nme, и битовая карта избыточна.
Выполните анализ, собрав статистику планировщика по таблице, и снова получите тот же план запроса.

Соберите актуальную статистику.
```
postgres@monman_db=# ANALYZE fatab;
```
```
ANALYZE
```
Проверьте обновленный план запроса.
```
postgres@monman_db=# EXPLAIN SELECT * FROM fatab WHERE nme > 1;
```
```
                             QUERY PLAN
--------------------------------------------------------------------------------
Index Scan using fatab_nme_idx on fatab  (cost=0.29..38.79 rows=1000 width=16)
Index Cond: (nme > '1'::double precision)
(2 rows)
```
После сбора статистики оценка количества строк стала точной — 1000 вместо ранее предсказанных 33362. Планировщик также выбрал Index Scan без битовой карты, поскольку знает, что данные в столбце nme упорядочены.

Включите автоочистку таблицы.

postgres@monman_db=# ALTER TABLE fatab SET (autovacuum_enabled = on);

ALTER TABLE

Самопроверка

Вопрос 1

В сеансе psql выполнены команды:

postgres@monman_db=# COPY fatab FROM '/tmp/fatab.sql';
COPY 100000

postgres@monman_db=# EXPLAIN SELECT * FROM fatab WHERE nme > 1;
QUERY PLAN
----------------------------------------------------------------------------------
Bitmap Heap Scan on fatab  (cost=674.60..1632.62 rows=33362 width=16)

Планировщик предсказал rows=33362, хотя реально строк с nme > 1 — 10000. Почему?

ANALYZE не был запущен после массовой загрузки данныхИндекс fatab_nme_idx поврежденVACUUM не был выполнен, и мертвые строки влияют на оценкуТип double precision не поддерживает статистику

Вопрос 2

В сеансе psql выполнены команды:

postgres@monman_db=# ANALYZE fatab;
ANALYZE

postgres@monman_db=# EXPLAIN SELECT * FROM fatab WHERE nme > 1;
QUERY PLAN
--------------------------------------------------------------------------------
Index Scan using fatab_nme_idx on fatab  (cost=0.29..38.79 rows=1000 width=16)
  Index Cond: (nme > '1'::double precision)
(2 rows)

После ANALYZE оценка rows стала точной (1000), а метод сканирования изменился с Bitmap Heap Scan на Index Scan. Почему?

ANALYZE обновил статистику, и планировщик узнал, что данные упорядочены по nme — битовая карта стала избыточнойANALYZE удалил дубликаты строк из индексаBitmap Heap Scan недоступен после ANALYZE из-за ограничений памятиПланировщик всегда отдает предпочтение Index Scan при наличии индекса

Вопрос 3

Какие действия запускают сбор статистики планировщика?

Самопроверка​

Вопрос 1

Вопрос 2

Вопрос 3

Самопроверка