Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / PostgreSQL Новый топик    Ответить
 план выполнения (most_common_freqs и игнор селективности лидирующих полей индекса)  [new]
aceton
Member

Откуда:
Сообщений: 98
Есть таблица 3,6 млн записей, есть индекс по трем полям. Выборка из этой таблицы с условием по этим трем полям использует индекс. EXPLAIN показывает, что ожидается три записи в выборке, по факту имеем одну.
Берем и присоединяем к этой выборке (по тем же условиям) другую таблицу (очень большую) по первому полю одного из её индексов. И теперь постгрес из первой таблицы достает данные через Parallel Seq Scan (конечно же, медленно).
А потом уже делает для каждого из трех воркеров Nested Loop.
Засовываю выборку из первой таблицы в материализованное CTE, и всё работает как надо.

По поводу индекса и условий отбора: значения для второго и третьего полей индекса отметились в стате с most_common_freqs 0.43 и 0.42 соответственно. Но значение для первого поля (тоже просто константа в запросе) в стате не отметилось и, собсно, решает. В опыте выше достаточно указать в условии для третьего(!) поля индекса значение с низкой частотой, чтобы индекс начал работать.

Почему постгрес так делает? Как заставить его применить индекс без вытаскивания части выборки в материализованное CTE?
26 окт 21, 20:37    [22388496]     Ответить | Цитировать Сообщить модератору
 Re: план выполнения (most_common_freqs и игнор селективности лидирующих полей индекса)  [new]
Maxim Boguk
Member

Откуда: По разному.
Сообщений: 5021
aceton
Есть таблица 3,6 млн записей, есть индекс по трем полям. Выборка из этой таблицы с условием по этим трем полям использует индекс. EXPLAIN показывает, что ожидается три записи в выборке, по факту имеем одну.
Берем и присоединяем к этой выборке (по тем же условиям) другую таблицу (очень большую) по первому полю одного из её индексов. И теперь постгрес из первой таблицы достает данные через Parallel Seq Scan (конечно же, медленно).
А потом уже делает для каждого из трех воркеров Nested Loop.
Засовываю выборку из первой таблицы в материализованное CTE, и всё работает как надо.

По поводу индекса и условий отбора: значения для второго и третьего полей индекса отметились в стате с most_common_freqs 0.43 и 0.42 соответственно. Но значение для первого поля (тоже просто константа в запросе) в стате не отметилось и, собсно, решает. В опыте выше достаточно указать в условии для третьего(!) поля индекса значение с низкой частотой, чтобы индекс начал работать.

Почему постгрес так делает? Как заставить его применить индекс без вытаскивания части выборки в материализованное CTE?


Если не сложно покажите запрос целиком и план (тоже целиком).
Я тогда смогу предметно позадавать вопросы и посоветовать.
Первоначальное предположение (исходя из "значение для первого поля (тоже просто константа в запросе) в стате не отметилось") у вас кривая оценка количества distinct значений в этом поле. А это достаточно легко фиксится.

--
Maxim Boguk
лучшая поддержка PostgreSQL: dataegret.ru
27 окт 21, 12:30    [22388676]     Ответить | Цитировать Сообщить модератору
 Re: план выполнения (most_common_freqs и игнор селективности лидирующих полей индекса)  [new]
aceton
Member

Откуда:
Сообщений: 98
Оценка distinct-значений не кривая. EXPLAIN простой выборки с условием только по первому полю индекса предсказывает 18 записей.

-- хорошо
select *
from main.account
where owner_id = 12345 and code = 2411::int2 and currency = 'TEST'

--Index Scan using account_owner_id_code_currency_idx on account a  (cost=0.43..4.67 rows=3 width=140) (actual time=0.079..0.081 rows=1 loops=1)
--  Index Cond: ((owner_id = '12345'::bigint) AND (code = '2411'::smallint) AND (currency = 'TEST'::text))
--Planning Time: 0.135 ms
--Execution Time: 0.115 ms


-- почему так?
select *
from main.account a
  join main.post p on p.debit_id = a.id
where a.owner_id = 12345 and a.code = 2411::int2 and a.currency = 'TEST'

--Gather  (cost=1000.71..8769077.25 rows=2140 width=201) (actual time=1759.120..1764.689 rows=0 loops=1)
--  Workers Planned: 2
--  Workers Launched: 2
--  ->  Nested Loop  (cost=0.70..8767863.25 rows=892 width=201) (actual time=1735.726..1735.727 rows=0 loops=3)
--        ->  Parallel Seq Scan on account a  (cost=0.00..103251.04 rows=1 width=140) (actual time=1732.715..1735.699 rows=0 loops=3)
--              Filter: ((owner_id = '12345'::bigint) AND (code = '2411'::smallint) AND (currency = 'TEST'::text))
--              Rows Removed by Filter: 1215308
--        ->  Index Scan using post_0_0_debit_id_ts_idx on post_0_0 p  (cost=0.70..8580683.44 rows=8392877 width=61) (actual time=0.055..0.055 rows=0 loops=1)
--              Index Cond: (debit_id = a.id)
--Planning Time: 0.729 ms
--JIT:
--  Functions: 24
--  Options: Inlining true, Optimization true, Expressions true, Deforming true
--  Timing: Generation 5.994 ms, Inlining 318.341 ms, Optimization 444.522 ms, Emission 305.928 ms, Total 1074.786 ms
--Execution Time: 1766.207 ms


-- снова хорошо
with a as materialized
(
  select *
  from main.account
  where owner_id = 12345 and code = 2411::int2 and currency = 'TEST'
)
select *
from a
  join main.post p on p.debit_id = a.id

--Nested Loop  (cost=5.37..25993541.06 rows=30273492 width=275) (actual time=170.289..170.291 rows=0 loops=1)
--  CTE a
--    ->  Index Scan using account_owner_id_code_currency_idx on account a_1  (cost=0.43..4.67 rows=3 width=140) (actual time=0.054..0.056 rows=1 loops=1)
--          Index Cond: ((owner_id = '12345'::bigint) AND (code = '2411'::smallint) AND (currency = 'TEST'::text))
--  ->  CTE Scan on a  (cost=0.00..0.06 rows=3 width=214) (actual time=0.055..0.058 rows=1 loops=1)
--  ->  Index Scan using post_0_0_debit_id_ts_idx on post_0_0 p  (cost=0.70..8580582.36 rows=8392975 width=61) (actual time=0.066..0.067 rows=0 loops=1)
--        Index Cond: (debit_id = a.id)
--Planning Time: 0.218 ms
--JIT:
--  Functions: 8
--  Options: Inlining true, Optimization true, Expressions true, Deforming true
--  Timing: Generation 1.226 ms, Inlining 8.509 ms, Optimization 80.447 ms, Emission 81.031 ms, Total 171.213 ms
--Execution Time: 171.586 ms
27 окт 21, 14:34    [22388816]     Ответить | Цитировать Сообщить модератору
 Re: план выполнения (most_common_freqs и игнор селективности лидирующих полей индекса)  [new]
Maxim Boguk
Member

Откуда: По разному.
Сообщений: 5021
aceton,

Проблема находится в районе строчки
-- -> Index Scan using post_0_0_debit_id_ts_idx on post_0_0 p (cost=0.70..8580683.44 rows=8392877 width=61) (actual time=0.055..0.055 rows=0 loops=1)
-- Index Cond: (debit_id = a.id)

База думает что по debit_id = a.id будем 8М строк а на самом деле 0.

Что у вас с распределением данных debit_id в таблице post_0_0 ?

Если не сложно

select count(*), count(debit_id), count(distinct debit_id) from post_0_0;
и
select cout(*), debit_id from post_0_0 group by debit_id order by 1 desc limit 10;
покажите.

У вас там явно какое то неожиданное распределение данных если база настолько ошибается в оценке селективности.

--
Maxim Boguk
лучшая поддержка PostgreSQL: dataegret.ru
27 окт 21, 15:27    [22388874]     Ответить | Цитировать Сообщить модератору
 Re: план выполнения (most_common_freqs и игнор селективности лидирующих полей индекса)  [new]
aceton
Member

Откуда:
Сообщений: 98
Maxim Boguk,

Таблица post_0_0 большая 226Gb и 2.6e9 записей (так в стате, но похоже на правду), n_distinct для debit_id - 310 (analyze делал). Но ведь речь о выборке из main.account. У нас в обоих ситуациях nested loop - и с CTE, и без. И еще вот это:
автор
В опыте выше достаточно указать в условии для третьего(!) поля индекса значение с низкой частотой, чтобы индекс начал работать.

И это тоже про main.account и про его индекс, который оказался по непонятным причинам в игноре.
Запрошенные выборки из post_0_0 сделать весьма проблематично.
27 окт 21, 17:56    [22389014]     Ответить | Цитировать Сообщить модератору
 Re: план выполнения (most_common_freqs и игнор селективности лидирующих полей индекса)  [new]
Maxim Boguk
Member

Откуда: По разному.
Сообщений: 5021
aceton
Maxim Boguk,

Таблица post_0_0 большая 226Gb и 2.6e9 записей (так в стате, но похоже на правду), n_distinct для debit_id - 310 (analyze делал). Но ведь речь о выборке из main.account. У нас в обоих ситуациях nested loop - и с CTE, и без. И еще вот это:
автор
В опыте выше достаточно указать в условии для третьего(!) поля индекса значение с низкой частотой, чтобы индекс начал работать.

И это тоже про main.account и про его индекс, который оказался по непонятным причинам в игноре.
Запрошенные выборки из post_0_0 сделать весьма проблематично.


Индекс не оказался в игноре... цена запроса через CTE в 3 раза больше чем через parralel seq scan
cost=5.37..25993541.06 vs cost=1000.71..8769077.25

и если бы база УМЕЛА parralel CTE scan - она бы его применила.
Аналогично если бы в ответе было не 0 строк а ожидаемые 30M cтрок - запрос с seq scan и паралельным выполнением был бы вероятно быстрее.

Из имеющихся у базы данных она строит разумный план. Она никак не может знать что в post_0_0 по найденному a.id будет ноль строк.

--
Maxim Boguk
лучшая поддержка PostgreSQL: dataegret.ru
27 окт 21, 18:31    [22389036]     Ответить | Цитировать Сообщить модератору
 Re: план выполнения (most_common_freqs и игнор селективности лидирующих полей индекса)  [new]
Maxim Boguk
Member

Откуда: По разному.
Сообщений: 5021
aceton,

А какая версия базы у вас? Тут в принципе parralel index scan напрашивается.


--
Maxim Boguk
лучшая поддержка PostgreSQL: dataegret.ru
27 окт 21, 18:43    [22389041]     Ответить | Цитировать Сообщить модератору
 Re: план выполнения (most_common_freqs и игнор селективности лидирующих полей индекса)  [new]
aceton
Member

Откуда:
Сообщений: 98
Maxim Boguk,

PostgreSQL v.12.4 (Ubuntu 12.4-1.pgdg18.04+1)

Я что-то нить потерял. Сервер решил получить данные из одного источника, а потом для каждой строчки поискать в другом. Первый - account. Условия одинаковые при обычной выборке и при выборке с дальнейшим Nested Loop. На этом этапе при чем здесь post_0_0? Как он насчитал такую стоимость через CTE относительно прямого варианта?
Если я в условии поиска по account поменяю значение третьей(!) колонки в индексе (currency) на значение с маленьким most_common_freqs, то запрос без CTE применяет индекс. Повторюсь - выборка просто по owner_id ожидается в количестве 18 записей. Это первое поле индекса.
27 окт 21, 19:20    [22389060]     Ответить | Цитировать Сообщить модератору
 Re: план выполнения (most_common_freqs и игнор селективности лидирующих полей индекса)  [new]
Maxim Boguk
Member

Откуда: По разному.
Сообщений: 5021
aceton
Maxim Boguk,

PostgreSQL v.12.4 (Ubuntu 12.4-1.pgdg18.04+1)

Я что-то нить потерял. Сервер решил получить данные из одного источника, а потом для каждой строчки поискать в другом. Первый - account. Условия одинаковые при обычной выборке и при выборке с дальнейшим Nested Loop. На этом этапе при чем здесь post_0_0? Как он насчитал такую стоимость через CTE относительно прямого варианта?
Если я в условии поиска по account поменяю значение третьей(!) колонки в индексе (currency) на значение с маленьким most_common_freqs, то запрос без CTE применяет индекс. Повторюсь - выборка просто по owner_id ожидается в количестве 18 записей. Это первое поле индекса.


Смотрите база думает что по условию where a.owner_id = 12345 and a.code = 2411::int2 and a.currency = 'TEST'
будет 3 строки выбрано
и для каждой из них будет выбрано по 8M строк по условию (debit_id = a.id)
сама выборка этих 8М строк - штука крайне тяжелая поэтому логично запустить параллельное выполнение этого запроса
чтобы эти выборки по 8М строк выбирались в несколько потоков.
Но 12 версия НЕ УМЕЕТ в parralel index scan а только в parralel seq scan, поэтому база логично делает паралельный seq scan по main.account.

Через CTE такая стоимость потому что база не может в 12 версии паралельное выполнение запроса включить на такой ситуации.
И честно насчитывает цену в 3 раза выше.

Если же "Если я в условии поиска по account поменяю значение третьей(!) колонки в индексе (currency) на значение с маленьким most_common_freqs" база решит что по набору условий where a.owner_id = 12345 and a.code = 2411::int2 and a.currency = чтототам будет не 3 строки а 1 строка и в такой ситуации параллеьный join смысла делать нет.

Возможно тут бы помогло создание расширеной статисткики по полям owner_id,code,currency для уточнения прогноза по этим условиям.

PS: в таких случаях как ни странно часто лучший выход разбивать на 2 запроса (сначала выбирать a.id нужные а потом main.post по where IN (список)
для получения разумных планов если у вас данные очень неровно расположены.

--
Maxim Boguk
лучшая поддержка PostgreSQL: dataegret.ru
28 окт 21, 10:19    [22389245]     Ответить | Цитировать Сообщить модератору
 Re: план выполнения (most_common_freqs и игнор селективности лидирующих полей индекса)  [new]
aceton
Member

Откуда:
Сообщений: 98
Maxim Boguk,

Если выборка из post_0_0 показалась тяжелой, то почему не выполнять параллельно узел чтения из post_0_0 вместо раскладывания на воркеры всего запроса. Parallel Index Scan появился существенно раньше. В целом понятно, что субд вынужденно не применила параллельное выполнение и случайно выиграла. А когда хотела ускориться за счет распараллеливания, то промахнулась, но в рамках одного воркера при параллельной обработке выбор Seq Scan по счетам непонятен.

Понятно, что можно поприседать и сделать быстро. Проблема в том, что поведение довольно неожиданное. Предсказывать его крайне затруднительно, а если все запросы разбивать на кусочки, то весь смысл теряется.
28 окт 21, 11:25    [22389286]     Ответить | Цитировать Сообщить модератору
 Re: план выполнения (most_common_freqs и игнор селективности лидирующих полей индекса)  [new]
Maxim Boguk
Member

Откуда: По разному.
Сообщений: 5021
aceton
Maxim Boguk,

Если выборка из post_0_0 показалась тяжелой, то почему не выполнять параллельно узел чтения из post_0_0 вместо раскладывания на воркеры всего запроса. Parallel Index Scan появился существенно раньше. В целом понятно, что субд вынужденно не применила параллельное выполнение и случайно выиграла. А когда хотела ускориться за счет распараллеливания, то промахнулась, но в рамках одного воркера при параллельной обработке выбор Seq Scan по счетам непонятен.

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


Потому что параллельное выполнение в месте "выполнять параллельно узел чтения из post_0_0" база не умеет даже в 14той версии.


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


--
Maxim Boguk
лучшая поддержка PostgreSQL: dataegret.ru
28 окт 21, 12:50    [22389336]     Ответить | Цитировать Сообщить модератору
Все форумы / PostgreSQL Ответить