Гость
Войти | Профиль | Очистить
Действия ...
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Оптимизатор и CTE / 10 сообщений из 10, страница 1 из 1
08.05.2020, 08:57
    #39955176
kliff
kliff
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизатор и CTE
Добрый день. Ребята, прошу помочь разобраться с принципами работы оптимизатора в данной ситуации.

Есть таблица t_Form2User(id_form, id_user), которая связывает формы и пользователей. на id_form навешан индекс.

Выполняю два запроса.
Код: sql
1.
2.
3.
4.
with forms_ids as (select id from t_form where id = 4415 or id_parent in (select id_parent from t_form where id = 4415)
select distinct id_user u, create_data d 
from t_Form2User
where id_form in (select id from forms_ids);



Второй
Код: sql
1.
2.
3.
4.
with forms_ids as (select id from t_form where id in (4415, 4416, 4417, 4418, 4419))
select distinct id_user u, create_data d 
from t_Form2User
where id_form in (select id from forms_ids);



В первом запросе cte возвращает те же 5 id форм.

Было предположение, что в первом запросе оптимизатор материализует CTE и потом используя индекс по t_Form2User.id_form сходит к таблице t_Form2User.
Но получилось, что в первом запросе мы получаем фулскан таблицы t_Form2User, а во втором запросе он ходит в t_Form2User по индексу id_form.

Понятно, что на поведение оптимизатора влияет наличие OR в CTE.

Как работает оптимизатор в данном случае?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
08.05.2020, 10:39
    #39955197
Guzya
Guzya
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизатор и CTE
Покажите оба плана запросов.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
08.05.2020, 11:10
    #39955208
Maxim Boguk
Maxim Boguk
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизатор и CTE
kliff,

1)надо оба плана показать
2)указать какая версия (потому что поведение между 12 версией и более старыми - для CTE отличается сильно).
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
08.05.2020, 14:16
    #39955308
kliff
kliff
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизатор и CTE
Maxim Boguk
kliff,

1)надо оба плана показать
2)указать какая версия (потому что поведение между 12 версией и более старыми - для CTE отличается сильно).


Версия 9.6

Индекс он же pk CONSTRAINT t_Form2User_pk PRIMARY KEY (id_form, id_user)

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
1 запрос
HashAggregate  (cost=108181.37..111739.76 rows=355839 width=8)
  Group Key: t_Form2User.id_user, t_Form2User.create_data
  CTE forms
    ->  Seq Scan on t_form t_form_1  (cost=8.31..3901.22 rows=10798 width=4)
          Filter: ((id = 4415) OR (hashed SubPlan 1))
          SubPlan 1
            ->  Index Scan using t_form_id_idx on t_form  (cost=0.29..8.30 rows=1 width=4)
                  Index Cond: (id = 4415)
  ->  Hash Join  (cost=247.46..95384.20 rows=1779191 width=8)
        Hash Cond: (t_Form2User.id_form = forms.id)
        ->  Seq Scan on t_Form2User  (cost=0.00..81244.82 rows=3558382 width=12)
        ->  Hash  (cost=244.96..244.96 rows=200 width=4)
              ->  HashAggregate  (cost=242.96..244.96 rows=200 width=4)
                    Group Key: forms.id
                    ->  CTE Scan on forms  (cost=0.00..215.96 rows=10798 width=4)
					
2 запрос					
HashAggregate  (cost=4200.18..4211.14 rows=1096 width=8)
  Group Key: t_Form2User.id_user, t_Form2User.create_data
  CTE forms
    ->  Index Only Scan using t_form_pk on t_form  (cost=0.29..26.22 rows=4 width=4)
          Index Cond: (id = ANY ('{4415, 4416, 4417, 4418, 4419}'::integer[]))
  ->  Nested Loop  (cost=14.64..4168.47 rows=1096 width=8)
        ->  HashAggregate  (cost=0.09..0.13 rows=4 width=4)
              Group Key: forms.id
              ->  CTE Scan on forms  (cost=0.00..0.08 rows=4 width=4)
        ->  Bitmap Heap Scan on t_Form2User  (cost=14.55..1039.35 rows=274 width=12)
              Recheck Cond: (id_form = forms.id)
              ->  Bitmap Index Scan on t_Form2User_pk  (cost=0.00..14.48 rows=274 width=0)
                    Index Cond: (id_form = forms.id)
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
08.05.2020, 15:25
    #39955354
Guzya
Guzya
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизатор и CTE
Могу предположить,
что имея конкретные значения id = ANY ('{4415, 4416, 4417, 4418, 4419}' планировщик может посмотреть по ним статистику и сделать
более точное предположение о количестве результирующих строк. И соответственно принять решение, смотреть по индексу или всю таблицу.

В первом запросе планировщик предполагает получить rows=10798
Код: sql
1.
Seq Scan on t_form t_form_1  (cost=8.31..3901.22 rows=10798 width=4)



А во втором rows=4
Код: sql
1.
 Index Only Scan using t_form_pk on t_form  (cost=0.29..26.22 rows=4 width=4)
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
08.05.2020, 19:40
    #39955452
Swa111
Swa111
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизатор и CTE
kliff,

А если так?

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
with forms_ids as (
select id_parent  id from t_form where id = 4415
union all
select 4415)

select distinct id_user u, create_data d 
from t_Form2User
where id_form in (select id from forms_ids);
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
11.05.2020, 15:17
    #39955905
kliff
kliff
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизатор и CTE
Swa111
kliff,

А если так?

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
with forms_ids as (
select id_parent  id from t_form where id = 4415
union all
select 4415)

select distinct id_user u, create_data d 
from t_Form2User
where id_form in (select id from forms_ids);


Такой запрос я пробовал, он по индексам отрабатывает.


Но у меня все-таки вопрос не как переписать запрос, а прояснить работу оптимизатора на конкретных примерах чтобы избавиться от недопонимания.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
12.05.2020, 03:18
    #39956066
Maxim Boguk
Maxim Boguk
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизатор и CTE
kliff,

В первом случае оптимизатор думает что он получит
-> CTE Scan on forms (cost=0.00..215.96 rows=10798 width=4)
10800 строк из CTE

а во втором случае думает что получит
-> CTE Scan on forms (cost=0.00..0.08 rows=4 width=4)


И исходя из своей статистики по таблице t_Form2User решает что в первом случае join будет быстрее сделать через hash join потому что надо join нить с 2500 большим количеством строк.

cte тут влияет достаточно слабо... с тем же успехом можно и без cte а с обычным подзапросом получить тот же эффект.

Проблема в оценке количества строк по OR условию, она всегда почти проблемная.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
13.05.2020, 09:57
    #39956664
kliff
kliff
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизатор и CTE
Большое всем спасибо за участие.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
19.05.2020, 15:14
    #39959530
kliff
kliff
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизатор и CTE
kliff,

добавлю, что постриг еще дает интересные эффекты
если вместо
where id_form in (select id from forms_ids);

писать
where id_form = any (array(select id from forms_ids));

часто это приводит к тому, что начинает использоваться индекс
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Оптимизатор и CTE / 10 сообщений из 10, страница 1 из 1
Целевая тема:
Создать новую тему:
Автор:
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
созд. / загр.: 398ms
Закрыть
start [/forum/topic.php?fid=53&tablet=1&tid=1994679]:
 0ms
get settings:
 10ms
get forum list:
 15ms
check forum access:
 4ms
check topic access:
 4ms
track hit:
 29ms
get topic data:
 11ms
get forum data:
 2ms
get page messages:
 49ms
get tp. blocked users:
 2ms
others: 272ms
total:  398ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]