postgresql 14(alt linux) - Быстрая деградация статистики в запросе

@oktogen · Регистрация: 30.06.2022

Студворк — интернет-сервис помощи студентам

Есть запрос

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
EXPLAIN ANALYZE
SELECT
o.id AS id_object,
o.i_id AS i_id_object,
ot.id_triangle,
t.i_id AS i_id_triangle
FROM
sh_computing_field.tbl_rooms_triangles_c ot
JOIN sh_computing_field.tbl_rooms_c  o ON o.id_calc = ot.id_calc AND
o.id = ot.id_room AND
o.oper_type <> 3
JOIN sh_computing_field.tbl_triangle_c t ON ot.id_triangle = t.id AND
ot.id_calc = t.id_calc AND
t.oper_type <> 3
JOIN sh_computing_field.tbl_vertex_c  v1 ON t.id_vertex1 = v1.id AND
v1.id_calc = ot.id_calc AND
v1.oper_type <> 3
JOIN sh_computing_field.tbl_vertex_c v2 ON  t.id_vertex2 = v2.id AND
v2.id_calc = ot.id_calc AND
v2.oper_type <> 3
JOIN sh_computing_field.tbl_vertex_c  v3 ON t.id_vertex3 = v3.id AND
v3.id_calc = ot.id_calc AND
v3.oper_type <> 3
WHERE ot.id_calc =current_setting('var.id_calc')::UUID AND ot.oper_type <> 3;

который не даёт покоя.
До этого на сервере были серьёзные аппаратные заморочки, которые не позволяли выявить проблему,
думали на них. Но проблема осталась.
Этот запрос тормозит на порядок.

postgresql 14(alt linux) - Быстрая деградация статистики в запросе

Если сказать серверу: ANALYZE sh_computing_field.tbl_rooms_triangles_c ;
ситуация улучшается

но на очень короткое время, и через пару часов всё возвращается обратно.
Вопрос: как это лечить?
В postgrespro есть модуль online_analyze, но тут postgresql обычный.
Может есть другое решение?

Кликните здесь для просмотра всего текста

SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
CREATE TABLE sh_computing_field.tbl_rooms_triangles_c (
    id_calc uuid NOT NULL,
    id_room uuid NOT NULL,
    id_triangle uuid NOT NULL,
    oper_id_user uuid,
    oper_ip inet,
    oper_date TIMESTAMP WITHOUT TIME zone NOT NULL,
    oper_type SMALLINT NOT NULL
);
ALTER TABLE sh_computing_field.tbl_rooms_triangles_c ADD CONSTRAINT key_pk_room_triangles_c_all
  PRIMARY KEY (id_calc, id_room, id_triangle);
ALTER TABLE sh_computing_field.tbl_rooms_triangles_c ADD CONSTRAINT key_fk_tbl_room_triangles_c_id_triangle
  FOREIGN KEY (id_calc, id_triangle) REFERENCES sh_computing_field.tbl_triangle_c(id_calc, id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED;
ALTER TABLE sh_computing_field.tbl_rooms_triangles_c ADD CONSTRAINT key_fk_tbl_room_triangles_c_id_room
  FOREIGN KEY (id_calc, id_room) REFERENCES sh_computing_field.tbl_rooms_c(id_calc, id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED;
ALTER TABLE sh_computing_field.tbl_rooms_triangles_c ADD CONSTRAINT key_fk_tbl_room_triangles_c_oper_id_user
  FOREIGN KEY (oper_id_user) REFERENCES sh_admin.tbl_user_c(id);
ALTER TABLE sh_computing_field.tbl_rooms_triangles_c ADD CONSTRAINT key_fk_tbl_room_triangles_c_oper_type
  FOREIGN KEY (oper_type) REFERENCES sh_systems.tbl_sys_oper_type(id);
CREATE INDEX idx_room_triangles_c_id_room ON sh_computing_field.tbl_rooms_triangles_c USING btree (id_room);
CREATE INDEX idx_room_triangles_c_id_triangle ON sh_computing_field.tbl_rooms_triangles_c USING btree (id_triangle);

ЗЫ
Индексы перепробовал всякие.
sh_computing_field.tbl_rooms_triangles_c - 35794877 записей
sh_computing_field.tbl_triangle_c - 93111317 записей

@fte65 · 01.11.2022, 14:05

Автовакуум сделать агрессивнее, например:

SQL
1
ALTER TABLE sh_computing_field SET(autovacuum_vacuum_scale_factor=0.001);

@oktogen · 01.11.2022, 15:09 **[ТС]**

Сообщение от fte65

Автовакуум сделать агрессивнее

Сделал

SQL
1
2
3
ALTER TABLE sh_computing_field.tbl_rooms_triangles_c SET(autovacuum_vacuum_scale_factor=0.001);
ALTER TABLE sh_computing_field.tbl_vertex_c SET(autovacuum_vacuum_scale_factor=0.001);
ALTER TABLE sh_computing_field.tbl_rooms_c SET(autovacuum_vacuum_scale_factor=0.001);

Погляжу что получится.

Добавлено через 1 час 0 минут

SQL
1
2
3
ALTER TABLE sh_computing_field.tbl_rooms_triangles_c SET(autovacuum_analyze_threshold=10);
ALTER TABLE sh_computing_field.tbl_vertex_c SET(autovacuum_analyze_threshold=10);
ALTER TABLE sh_computing_field.tbl_rooms_c SET(autovacuum_analyze_threshold=10);

@PaulWist · 01.11.2022, 15:47

По хорошему, надо избавиться от

SQL
1
oper_type <> 3

@oktogen · 01.11.2022, 16:06 **[ТС]**

Сообщение от PaulWist

По хорошему, надо избавиться

Тесты кажут, что это ничего практически не даст.
Пока самый рабочий вариант - строгать замену online_analyze,
Может ещё статистики создать и попробовать как планер себя с ними поведёт.

@PaulWist · 01.11.2022, 16:37

Вот этот кусок

SQL
1
2
3
4
5
6
7
8
9
JOIN sh_computing_field.tbl_vertex_c  v1 ON t.id_vertex1 = v1.id AND
v1.id_calc = ot.id_calc AND
v1.oper_type <> 3
JOIN sh_computing_field.tbl_vertex_c v2 ON  t.id_vertex2 = v2.id AND
v2.id_calc = ot.id_calc AND
v2.oper_type <> 3
JOIN sh_computing_field.tbl_vertex_c  v3 ON t.id_vertex3 = v3.id AND
v3.id_calc = ot.id_calc AND
v3.oper_type <> 3

можно переписать на

SQL
1
2
3
4
5
JOIN sh_computing_field.tbl_vertex_c  v1 ON 
t.id_vertex1 = v1.id AND
t.id_vertex2 = v1.id AND
t.id_vertex3 = v1.id AND
v1.oper_type <> 3

либо на exists(), поскольку результате нет полей из этой таблицы.

@oktogen · 01.11.2022, 17:27 **[ТС]**

Итак, похоже, что exists подходит.

SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
EXPLAIN ANALYZE
SELECT
  o.id AS id_object,
  o.i_id AS i_id_object,
  ot.id_triangle,
  t.i_id AS i_id_triangle
FROM
  sh_computing_field.tbl_rooms_triangles_c ot
  JOIN sh_computing_field.tbl_rooms_c  o ON o.id_calc = ot.id_calc AND o.id = ot.id_room AND o.oper_type <> 3
  JOIN sh_computing_field.tbl_triangle_c t ON ot.id_triangle = t.id AND ot.id_calc = t.id_calc AND t.oper_type <> 3
WHERE 
  ot.id_calc =current_setting('var.id_calc')::UUID AND 
  ot.oper_type <> 3 AND
  EXISTS(SELECT 1 FROM sh_computing_field.tbl_vertex_c v WHERE v.id_calc = ot.id_calc AND v.id  IN(t.id_vertex1,t.id_vertex2,t.id_vertex3) AND v.oper_type<>3);

Code
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
                                                                                                  QUERY PLAN                                                                                                   
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Merge Join  (cost=1002.16..1502391.52 rows=4634933 width=40) (actual time=5.031..285.179 rows=11800 loops=1)
   Merge Cond: (o.id = ot.id_room)
   ->  Index Scan using key_pk_rooms_c_id_ccnew on tbl_rooms_c o  (cost=0.43..632.09 rows=564 width=36) (actual time=0.071..1.676 rows=503 loops=1)
         Index Cond: (id_calc = (current_setting('var.id_calc'::text))::uuid)
         Filter: (oper_type <> 3)
   ->  Materialize  (cost=1001.73..1436343.09 rows=1643591 width=52) (actual time=4.949..222.344 rows=11800 loops=1)
         ->  Gather Merge  (cost=1001.73..1432234.11 rows=1643591 width=52) (actual time=4.938..162.274 rows=11800 loops=1)
               Workers Planned: 1
               Workers Launched: 1
               ->  Nested Loop Semi Join  (cost=1.72..1246330.11 rows=966818 width=52) (actual time=0.264..198.654 rows=5900 loops=2)
                     ->  Nested Loop  (cost=1.15..24056.18 rows=1104935 width=100) (actual time=0.154..128.662 rows=5900 loops=2)
                           ->  Parallel Index Scan using key_pk_room_triangles_c_all_ccnew on tbl_rooms_triangles_c ot  (cost=0.57..13265.84 rows=7046 width=48) (actual time=0.059..17.475 rows=5900 loops=2)
                                 Index Cond: (id_calc = (current_setting('var.id_calc'::text))::uuid)
                                 Filter: (oper_type <> 3)
                           ->  Index Scan using key_pk_tbl_triangle_c_tbl_triangles_c_id on tbl_triangle_c t  (cost=0.57..2.78 rows=1 width=84) (actual time=0.006..0.009 rows=1 loops=11800)
                                 Index Cond: ((id_calc = (current_setting('var.id_calc'::text))::uuid) AND (id = ot.id_triangle))
                                 Filter: (oper_type <> 3)
                     ->  Index Only Scan using key_pk_tbl_vertex_c_id on tbl_vertex_c v  (cost=0.57..4.62 rows=1 width=32) (actual time=0.004..0.004 rows=1 loops=11800)
                           Index Cond: ((id_calc = (current_setting('var.id_calc'::text))::uuid) AND (id = ANY (ARRAY[t.id_vertex1, t.id_vertex2, t.id_vertex3])))
                           Heap Fetches: 0
 Planning Time: 5.535 ms
 Execution Time: 314.933 ms
(22 строки)

Вполе сносно.
Полез тестить и внедрять.
Всем спасибо.

Новые блоги и статьи Все статьи Все блоги /
Управление камерой с помощью скрипта OrbitControls.js на Three.js: Вращение, зум и панорамирование 8Observer8 05.03.2026 Содержание блога Финальная демка в браузере работает на Desktop и мобильных браузерах. Итоговый код: orbit-controls-threejs-js. zip. Сканируйте QR-код на мобильном. Вращайте камеру одним пальцем,. . .	SDL3 для Web (WebAssembly): Синхронизация спрайтов SDL3 и тел Box2D 8Observer8 04.03.2026 Содержание блога Финальная демка в браузере. Итоговый код: finish-sync-physics-sprites-sdl3-c. zip На первой гифке отладочные линии отключены, а на второй включены:. . .	SDL3 для Web (WebAssembly): Идентификация объектов на Box2D v3 - использование userData и событий коллизий 8Observer8 02.03.2026 Содержание блога Финальная демка в браузере. Итоговый код: finish-collision-events-sdl3-c. zip Сканируйте QR-код на мобильном и вы увидите, что появится джойстик для управления главным героем. . . .	Реалии Hrethgir 01.03.2026 Нет, я не закончил до сих пор симулятор. Эта задача сложнее. Не получилось уйти в плавсостав, но оно и к лучшему, возможно. Точнее получалось - но сварщиком в палубную команду, а это значит, в моём. . .
Ритм жизни kumehtar 27.02.2026 Иногда приходится жить в ритме, где дел становится всё больше, а вовлечения в происходящее — всё меньше. Плотный график не даёт вниманию закрепиться ни на одном событии. Утро начинается с быстрых,. . .	SDL3 для Web (WebAssembly): Сборка библиотек: SDL3, Box2D, FreeType, SDL3_ttf, SDL3_mixer и SDL3_image из исходников с помощью CMake и Emscripten 8Observer8 27.02.2026 Недавно вышла версия 3. 4. 2 библиотеки SDL3. На странице официальной релиза доступны исходники, готовые DLL (для x86, x64, arm64), а также библиотеки для разработки под Android, MinGW и Visual Studio. . . .	SDL3 для Web (WebAssembly): Реализация движения на Box2D v3 - трение и коллизии с повёрнутыми стенами 8Observer8 20.02.2026 Содержание блога Box2D позволяет легко создать главного героя, который не проходит сквозь стены и перемещается с заданным трением о препятствия, которые можно располагать под углом, как верхнее. . .	Конвертировать закладки radiotray-ng в m3u-плейлист damix 19.02.2026 Это можно сделать скриптом для PowerShell. Использование . \СonvertRadiotrayToM3U. ps1 <path_to_bookmarks. json> Рядом с файлом bookmarks. json появится файл bookmarks. m3u с результатом. # Check if. . .

postgresql 14(alt linux) - Быстрая деградация статистики в запросе

Решение