ReSQL.ru
     
powered by simpleCommunicator - 2.0.40     © 2025 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / запросы в цикле и plan_cache_mode
3 сообщений из 3, страница 1 из 1
запросы в цикле и plan_cache_mode
    #40071663
Misha111
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Misha111
Гость
добрый день.
наткнулся на фичу при кешировании планов запросов в пгскл.

подготовка данных:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
drop table t_tmp;
create table t_tmp as
select s k1, s k2, 0 f1
from ( 
select generate_series(1,1e6) s
) a;
create index on t_tmp (k1, k2);
analyze t_tmp;



выполняем блок1:
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
DO 
$$declare
    e_cur           RECORD;
begin
    for e_cur in (
        select distinct k1 from t_tmp
        order by k1
        limit 100
      ) loop
        delete from t_tmp a
         where k1=e_cur.k1
           and exists (
                SELECT null
                  from t_tmp b
                 where f1=1
                   and b.k1=e_cur.k1
                   and b.k2=a.k2
               );
    end loop;
END$$;



результат:
Код: plaintext
1.
DO
Время: 8,896 мс

выполняем блок2 через динамический скл:
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
DO 
$$declare
    e_cur           RECORD;
begin
    for e_cur in (
        select distinct k1 from t_tmp
        order by k1
        limit 100
      ) loop
    execute 
       'delete from t_tmp a
         where k1=$1
           and exists (
                SELECT null
                  from t_tmp b
                 where f1=1
                   and b.k1=$1
                   and b.k2=a.k2
               )'
        using e_cur.k1;
    end loop;
END$$;



результат:
Код: plaintext
1.
DO
Время: 27,382 мс

это ожидаемо и понятно.
делаем данные более похожими на реальные:
Код: sql
1.
2.
3.
4.
5.
6.
insert into t_tmp
select 1+(s/1e3)::int k1, s k2, 0 f1
from ( 
select generate_series(1,1e4) s
) a;
analyze t_tmp;



выполняем блок1:
Код: plaintext
1.
DO
Время: 1165,340 мс (00:01,165)

выполняем блок2:
Код: plaintext
1.
DO
Время: 29,421 мс

не понимаем и лопатим доку:
докаПо умолчанию (то есть когда plan_cache_mode имеет значение auto), сервер автоматически выбирает, использовать ли для подготовленного оператора с параметрами общий или специализированный план. На данный момент это происходит по следующему принципу — первые пять выполнений производятся со специализированными планами и вычисляется средняя стоимость этих планов. Затем строится общий план и его примерная стоимость сравнивается со средней стоимостью специализированных. При последующих выполнениях общий план будет использоваться, если его стоимость, по сравнению со стоимостью специализированных, не настолько велика, чтобы оправдать повторное планирование.

смотрим настройки:
Код: plaintext
1.
2.
3.
ce2pg2=> show  plan_cache_mode;
 plan_cache_mode
-----------------
 auto

пробуем поколдовать:
Код: plaintext
1.
2.
3.
select set_config( 'plan_cache_mode', 'force_custom_plan', false);
    set_config
-------------------
 force_custom_plan

еще раз выполняем блок1:
Код: plaintext
1.
DO
Время: 29,529 мс

выполняем блок2:
Код: plaintext
1.
DO
Время: 29,520 мс


не понятны 2 вещи:
1. почему сервер так сильно ошибается при выборе использования плана
(пробовал формировать данные чтобы первые 5 запросов выполнялись на смешанных данных - результат при plan_cache_mode=auto не меняется)
2. получается в последнем тесте динамический скл не несет видимых накладных расходов?


ЗЫ. вот планы которые используются для этого запроса при разных параметрах:

Код: plaintext
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.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
ce2pg2=> \set 1 1
ce2pg2=> explain analyze
ce2pg2->         delete from t_tmp a
ce2pg2->          where k1=:1
ce2pg2->            and exists (
ce2pg2(>                 SELECT null
ce2pg2(>                   from t_tmp b
ce2pg2(>                  where f1=1
ce2pg2(>                    and b.k1=:1
ce2pg2(>                    and b.k2=a.k2
ce2pg2(>                );
                                                                  QUERY PLAN

------------------------------------------------------------------------------------------------------------------------
----------------------
 Delete on t_tmp a  (cost=946.76..954.80 rows=1 width=12) (actual time=0.285..0.287 rows=0 loops=1)
   ->  Nested Loop  (cost=946.76..954.80 rows=1 width=12) (actual time=0.283..0.284 rows=0 loops=1)
         ->  HashAggregate  (cost=946.33..946.34 rows=1 width=12) (actual time=0.282..0.283 rows=0 loops=1)
               Group Key: b.k2
               ->  Index Scan using t_tmp_k1_k2_idx on t_tmp b  (cost=0.42..946.33 rows=1 width=12) (actual time=0.280..
0.281 rows=0 loops=1)
                     Index Cond: (k1 = '1'::numeric)
                     Filter: (f1 = 1)
                     Rows Removed by Filter: 500
         ->  Index Scan using t_tmp_k1_k2_idx on t_tmp a  (cost=0.42..8.45 rows=1 width=12) (never executed)
               Index Cond: ((k1 = '1'::numeric) AND (k2 = b.k2))
 Planning Time: 0.500 ms
 Execution Time: 0.369 ms
(12 строк)


Время: 2,029 мс
ce2pg2=> \set 1 100
ce2pg2=> explain analyze
ce2pg2->         delete from t_tmp a
ce2pg2->          where k1=:1
ce2pg2->            and exists (
ce2pg2(>                 SELECT null
ce2pg2(>                   from t_tmp b
ce2pg2(>                  where f1=1
ce2pg2(>                    and b.k1=:1
ce2pg2(>                    and b.k2=a.k2
ce2pg2(>                );
                                                              QUERY PLAN

------------------------------------------------------------------------------------------------------------------------
--------------
 Delete on t_tmp a  (cost=0.85..16.90 rows=1 width=12) (actual time=0.055..0.056 rows=0 loops=1)
   ->  Nested Loop Semi Join  (cost=0.85..16.90 rows=1 width=12) (actual time=0.053..0.054 rows=0 loops=1)
         Join Filter: (a.k2 = b.k2)
         ->  Index Scan using t_tmp_k1_k2_idx on t_tmp a  (cost=0.42..8.44 rows=1 width=12) (actual time=0.038..0.038 ro
ws=1 loops=1)
               Index Cond: (k1 = '100'::numeric)
         ->  Index Scan using t_tmp_k1_k2_idx on t_tmp b  (cost=0.42..8.45 rows=1 width=12) (actual time=0.012..0.012 ro
ws=0 loops=1)
               Index Cond: (k1 = '100'::numeric)
               Filter: (f1 = 1)
               Rows Removed by Filter: 1
 Planning Time: 0.422 ms
 Execution Time: 0.113 ms
(11 строк)


Время: 1,503 мс

и на всякий версия:
PostgreSQL 12.7 (Debian 12.7-1.pgdg100+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit
...
Рейтинг: 0 / 0
20.05.2021, 14:12
| Ответить | Цитировать | Написать  
запросы в цикле и plan_cache_mode
    #40071682
Фотография Maxim Boguk
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Maxim Boguk
Участник
Misha111,

Если у вас в процессе работы хранимки сильно меняется содержимое таблицы то закешированный custom plan может по мере работы стать сильно менее эффективным что у вас и наблюдается.

И даже autovacuum тут вас не спасет потому что все в пределах одной транзакции делается.

Сама по себе идея в цикле так поступать с таблицей не делая промежуточных commits - она является паталогической для базы.

Более того тоже самое может и с custom планом произойти легко. В данном случае просто получилось что custom plan в этой ситуации не деградирует (это скорее случайность при такой задаче).


--
Maxim Boguk
лучшая поддержка PostgreSQL: dataegret.ru
...
Рейтинг: 0 / 0
20.05.2021, 14:50
| Ответить | Цитировать | Написать  
запросы в цикле и plan_cache_mode
    #40071699
Misha111
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Misha111
Гость
Maxim Boguk,

Maxim BogukЕсли у вас в процессе работы хранимки сильно меняется содержимое таблицы то закешированный custom plan может по мере работы стать сильно менее эффективным что у вас и наблюдается.

тесткейс собран на коленке дабы отразить суть. в нем данные вообще не удаляются (фильтр f1=1, а в таблице все f1=0)
в оригинале есть таблица-назначение из которой удаляются записи которых нет в другой таблице, но при этом старшая часть ключей в источнике есть:

delete from t_dst a
where key1 in (select key1 from t_src)
and (key1, key2) not in (select key1, key2 from t_src)

решил проверить скорость если все делать в цикле по старшей части ключа key1 (опять же в примере выше запросы сильно упрощены).

т.о. проблема не в изменении данных в цикле, а именно в том что оптимизатор ошибается при выборе плана выполнения.
...
Рейтинг: 0 / 0
20.05.2021, 15:35
| Ответить | Цитировать | Написать  
3 сообщений из 3, страница 1 из 1
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / запросы в цикле и plan_cache_mode
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
NoSQL.ru             Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=53&msg=40071682&tid=1994028]:
 0ms
get settings:
 10ms
get forum list:
 13ms
check forum access:
 4ms
check topic access:
 4ms
track hit:
 36ms
get topic data:
 12ms
get forum data:
 3ms
get page messages:
 45ms
get tp. blocked users:
 2ms
others: 16ms
total:  145ms
созд. / загр.: 145ms
x
x
Закрыть


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