ReSQL.ru
     
powered by simpleCommunicator - 2.0.53     © 2025 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Оптимизатор и CTE
10 сообщений из 10, страница 1 из 1
Оптимизатор и CTE
    #39955176
kliff
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
kliff
Участник
Добрый день. Ребята, прошу помочь разобраться с принципами работы оптимизатора в данной ситуации.

Есть таблица 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, 08:57
| Ответить | Цитировать | Написать  
Оптимизатор и CTE
    #39955197
Guzya
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Guzya
Гость
Покажите оба плана запросов.
...
Рейтинг: 0 / 0
08.05.2020, 10:39
| Ответить | Цитировать | Написать  
Оптимизатор и CTE
    #39955208
Фотография Maxim Boguk
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Maxim Boguk
Участник
kliff,

1)надо оба плана показать
2)указать какая версия (потому что поведение между 12 версией и более старыми - для CTE отличается сильно).
...
Рейтинг: 0 / 0
08.05.2020, 11:10
| Ответить | Цитировать | Написать  
Оптимизатор и CTE
    #39955308
kliff
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
kliff
Участник
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, 14:16
| Ответить | Цитировать | Написать  
Оптимизатор и CTE
    #39955354
Guzya
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Guzya
Гость
Могу предположить,
что имея конкретные значения 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, 15:25
| Ответить | Цитировать | Написать  
Оптимизатор и CTE
    #39955452
Swa111
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
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
08.05.2020, 19:40
| Ответить | Цитировать | Написать  
Оптимизатор и CTE
    #39955905
kliff
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
kliff
Участник
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
11.05.2020, 15:17
| Ответить | Цитировать | Написать  
Оптимизатор и CTE
    #39956066
Фотография Maxim Boguk
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Maxim Boguk
Участник
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
12.05.2020, 03:18
| Ответить | Цитировать | Написать  
Оптимизатор и CTE
    #39956664
kliff
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
kliff
Участник
Большое всем спасибо за участие.
...
Рейтинг: 0 / 0
13.05.2020, 09:57
| Ответить | Цитировать | Написать  
Оптимизатор и CTE
    #39959530
kliff
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
kliff
Участник
kliff,

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

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

часто это приводит к тому, что начинает использоваться индекс
...
Рейтинг: 0 / 0
19.05.2020, 15:14
| Ответить | Цитировать | Написать  
10 сообщений из 10, страница 1 из 1
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Оптимизатор и CTE
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
NoSQL.ru       Новости, Чат       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=53&gotonew=1&tid=1994679]:
 0ms
get settings:
 10ms
get forum list:
 16ms
check forum access:
 4ms
check topic access:
 4ms
track hit:
 41ms
get topic data:
 13ms
get first new msg:
 8ms
get forum data:
 3ms
get page messages:
 55ms
get tp. blocked users:
 2ms
others: 277ms
total:  433ms
созд. / загр.: 433ms
x
x
Закрыть


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