Реализация стратегической цели

Стратегическая цель - интеграция PG_HAZEL и DeepSeek.

План

1. Производительность и ожидания СУБД в период перед инцидентом

2. Комплексный анализ инфраструктуры.

3. Корреляционный анализ ожиданий СУБД.

4. Рекомендации нейросети DeepSeek по снижению ожиданий.

5. Проблемные SQL запросы для оптимизации.

6. Анализ проблемного SQL запроса с использованием нейросети DeepSeek.

7. Рекомендации по оптимизации проблемного SQL запроса с использованием нейросети DeepSeek.

1. Производительности и ожидания СУБД в период перед инцидентом

2. Комплексный анализ инфраструктуры.

Корреляция ожиданий СУБД и метрик vmstat

Анализ метрик iostat для файловой системы /data

Чек-лист CPU

Чек-лист RAM

Результат

1. Имеются проблемы производительности диска , используемого для файловой системы /data.

2. Переключения контекста могут быть вызваны прерываниями IO.

3. Свободная RAM - менее 5%.

3.Корреляционные анализ ожиданий СУБД и выявление проблемных SQL запросов для оптимизации.

Корреляция и типы ожиданий

Ожидания типа IO

Ожидания типа LWLock

Диаграмма Парето(80%) по событиям ожиданий

4.Рекомендации нейросети DeepSeek по снижению ожиданий

DataFileRead

ParallelHashJoin

BufferMapping

LockManager

BufferContent

5. Проблемные SQL запросов для оптимизации.

Все проблемные запросы имеют ожидания DataFileRead . Причина - проблемы инфраструктуры.

Запросы имеющие ожидания типа LWLock

Запросы -583459049601560367 , -5849488707035427374 - проанализированы , ранее .

Запрос для оптимизации -4217442339750081997

6. Анализ проблемного SQL запроса с использованием нейросети DeepSeek.

Ключевые паттерны в SQL-запросе:

1. Точечная фильтрация по позиции

"table1"."col2" IN (871798)

- Фильтрация по конкретному col1 позиции (в отличие от предыдущих запросов со списками)

- Выборка данных для одной целевой позиции

2. Архитектура мягкого удаления (Soft Delete)

WHERE "table1"."col8" IS NULL

AND "table3"."col8" IS NULL

- Многоуровневая проверка на мягкое удаление

- Применяется к основной и связанным таблицам

3. Составные условия JOIN с бизнес-фильтрацией

LEFT OUTER JOIN ... ON "table1"."col1" = "table3"."table1col1"

AND ("table3"."col8" IS NULL

AND "table3"."col4" NOT IN (65, 66))

- Бизнес-логика в условиях JOIN, а не WHERE

- Исключение специфических workflow статусов (65, 66)

- Комбинация условий удаления и статусов

4. Система архивации записей

"table1"."col9" IS NULL

- Отдельный механизм архивации помимо мягкого удаления

- Дополнительный уровень управления жизненным циклом данных

5. LEFT JOIN для опциональных отношений

- `table2` - опциональная связь (риск ухода может отсутствовать)

- `table3` - опциональная связь с фильтрацией по статусам

6. Иерархическая модель данных

table1 (основа)

├── table2 (опциональный риск ухода)

└── table3 (преемники с фильтрацией)

- Древовидная структура с центральной сущностью table1

7. Паттерн именования алиасов

"table2"."col1" AS "table2.col1"

"table3"."col1" AS "table3.col1"

- Точечная нотация для представления отношений

- Четкое указание источника данных в результатах

8. Статусная модель workflow

- `col4` - управление рабочим процессом

- `col5` - статус кандидата

- `col6` - уровень готовности преемника

9. Экранирование идентификаторов

- Все имена в двойных кавычках для сохранения регистра

- Согласованное именование по всему запросу

Ключевые паттерны запроса

Этот запрос демонстрирует паттерн получения детализированных данных по конкретной позиции в системе управления преемственностью, с акцентом на актуальные, не архивные записи с активными преемниками.

7. Рекомендации по оптимизации проблемного SQL запроса с использованием нейросети DeepSeek.

1. Создание специализированных индексов

Для таблицы `table1`:

-- Составной индекс для условий WHERE

CREATE INDEX CONCURRENTLY col1x_succession_plan_main

ON "table1" ("col2", "col8", "col9")

WHERE "col8" IS NULL AND "col9" IS NULL;

-- Индекс для связи с table2

CREATE INDEX CONCURRENTLY col1x_succession_plan_risk_leaving

ON "table1" ("table2col1")

WHERE "col8" IS NULL;

Для таблицы `col7`:

-- Оптимизированный индекс для JOIN с фильтрацией

CREATE INDEX CONCURRENTLY col1x_col7_filtered

ON "col7" ("table1col1", "col4", "col8")

WHERE "col8" IS NULL AND "col4" NOT IN (65, 66);

-- Альтернативный вариант для лучшей селективности

CREATE INDEX CONCURRENTLY col1x_col7_main

ON "col7" ("table1col1")

INCLUDE ("col4", "col5", "col6")

WHERE "col8" IS NULL;

2. Оптимизация структуры запроса

Рассмотрите рефакторинг условия:

-- Текущий вариант может быть оптимизирован

WHERE "table1"."col8" IS NULL

AND "table1"."col2" IN (871798)

AND "table1"."col9" IS NULL

-- Убедитесь, что порядок условий соответствует индексам

3. Оптимизация JOIN операций

Для больших объемов данных рассмотрите:

-- Разделение запроса на два с использованием CTE

WITH filtered_plans AS (

SELECT "col1", "col2", "table2col1"

FROM "table1"

WHERE "col8" IS NULL

AND "col2" IN (871798)

AND "col9" IS NULL

)

SELECT

fp."col1", fp."col2",

rl."col1" AS "table2.col1",

rl."col3" AS "table2.col3",

s."col1" AS "table3.col1",

s."col4" AS "table3.col4",

s."col5" AS "table3.col5",

s."col6" AS "table3.col6"

FROM filtered_plans fp

LEFT JOIN "table2" rl ON fp."table2col1" = rl."col1"

LEFT JOIN "col7" s ON fp."col1" = s."table1col1"

AND s."col8" IS NULL

AND s."col4" NOT IN (65, 66);

4. Настройки PostgreSQL:

-- Увеличьте work_mem для лучшей сортировки и хэширования

SET work_mem = '256MB';

-- Убедитесь, что effective_cache_size правильно настроен

SHOW effective_cache_size;

5. Дополнительные рекомендации

-Статистика по индексам: Убедитесь, что `default_statistics_target` достаточно высок для точной оценки селективности

- Параллельные запросы: Проверьте настройки `max_parallel_workers_per_gather` для больших таблиц

Задача

Получить рекомендации нейросети по оптимизации СУБД и проблемных запросов выявленных в ходе анализа причин инцидента производительности СУБД.

Стратегическая цель - интеграция PG_HAZEL и DeepSeek.

Операционная скорость и ожидания СУБД

Корреляция типов ожиданий СУБД

События ожидания, составляющие 80% ожиданий СУБД по типам ожиданий (диаграмма Парето)

Рекомендации DeepSeek по снижению событий ожидания(сформированы заранее , формируются локально для заданных wait_event)

Формирование локальной базы знаний по рекомендациям нейросети

DeepSeek : рекомендации по оптимизации ожиданий СУБД PostgreSQL.

SQL запросы(queryid), составляющие 80% по количеству ожиданий СУБД по типам ожиданий (диаграмма Парето)

Ожидания типа IO

Ожидания типа IPC

Ожидания типа LWLock

SQL запрос для оптимизации -5849488707035427374

Рекомендации DeepSeek по оптимизации SQL запроса

1. Создание индексов

-- Для таблицы table1 (условия WHERE и JOIN)

CREATE INDEX CONCURRENTLY idx_table1_filter ON table1 (col2, col7, col8)

WHERE col7 IS NULL AND col8 IS NULL;

-- Для таблицы table2 (первичный ключ)

CREATE INDEX CONCURRENTLY idx_table2_col1 ON table2 (col1);

-- Для таблицы table4 (JOIN и условия)

CREATE INDEX CONCURRENTLY idx_table4_join ON table4 (table1col1, col7, col4)

WHERE col7 IS NULL AND col4 NOT IN (65, 66);

2. Упрощение условий

Перенесите условия из JOIN в WHERE, где это возможно, но для LEFT JOIN это нужно делать аккуратно:

SELECT ...

FROM table1

LEFT JOIN table2 ON table1.table2col1 = table2.col1

LEFT JOIN table4 AS table3 ON table1.col1 = table3.table1col1

WHERE

table1.col7 IS NULL

AND table1.col2 IN (...)

AND table1.col8 IS NULL

AND table3.col7 IS NULL -- Условие перенесено из JOIN

AND table3.col4 NOT IN (65,66)

3. Анализ и переписывание запроса

• Проверьте, не выполняется ли неявное преобразование типов в условиях JOIN

• Используйте EXISTS вместо IN, если список значений большой

• Разбейте запрос на CTE, если это упрощает логику

4. Дополнительные оптимизации

-- Обновите статистику после создания индексов

ANALYZE table1;

ANALYZE table2;

ANALYZE table4;

-- Проверьте настройки производительности

SET work_mem = '256MB'; -- Для операций сортировки и хэширования

5. Альтернативный вариант с подзапросом

WITH filtered_table1 AS (

SELECT col1, col2, table2col1

FROM table1

WHERE

col7 IS NULL

AND col2 IN (80214, 81665, ..., 564497)

AND col8 IS NULL

)

SELECT

t1.col1,

t1.col2,

t2.col1 AS "table2.col1",

t2.col3 AS "table2.col3",

t3.col1 AS "table3.col1",

...

FROM filtered_table1 t1

LEFT JOIN table2 t2 ON t1.table2col1 = t2.col1

LEFT JOIN table4 t3 ON

t1.col1 = t3.table1col1

AND t3.col7 IS NULL

AND t3.col4 NOT IN (65,66);

Для проверки эффективности:

1. Используйте EXPLAIN (ANALYZE, BUFFERS) для анализа плана выполнения

2. Следите за операциями Seq Scan -> они должны замениться на Index Scan

3. Проверьте оценку количества строк (rows) на каждом этапе

Особое внимание уделите селективности условия table1.col2 IN (...) - если выбирается больше 5-10% данных, полносканирование может быть эффективнее индекса. В этом случае рассмотрите индекс только на (col7, col8).

Также убедитесь, что в таблице table4 нет избыточных данных - условие col4 NOT IN (65,66) может быть вынесено в предварительную фильтрацию, если эти значения встречаются часто.

Начало работ и детали запроса

Оптимизация SQL-запросов PostgreSQL : LIKE (пример)

Оптимизация запросов с оператором LIKE в PostgreSQL — это важная задача, особенно при работе с большими объемами данных. Существует несколько эффективных подходов, которые кардинально меняют производительность в зависимости от конкретного шаблона поиска.

1. LIKE 'строка%' (Поиск по префиксу)

Рекомендуемый метод

B-tree индекс

Ключевые действия

Создать стандартный индекс: CREATE INDEX idx_name ON table (column);

2. ILIKE 'строка%' (Case-insensitive префикс)

Рекомендуемый метод

Функциональный B-tree индекс

Ключевые действия

Создать индекс на LOWER(column) и использовать LOWER(column) LIKE 'строка%'

3.LIKE '%строка' (Поиск по суффиксу)

Рекомендуемый метод

Индекс на обратной строке

Ключевые действия

Создать индекс: CREATE INDEX idx_name ON table (reverse(column));

и использовать reverse(column) LIKE reverse('%строка')

4. LIKE '%строка%' или ILIKE '%СтрОкА%' (Поиск по подстроке)

Рекомендуемый метод

GIN/GIST индекс с расширением pg_trgm

Ключевые действия

Включить расширение: CREATE EXTENSION pg_trgm;.

Создать индекс: CREATE INDEX idx_name ON table USING GIN (column gin_trgm_ops);

💡 Рекомендации по применению методов

1. Использование расширения pg_trgm для сложных шаблонов

Это самый универсальный способ для поиска, когда искомая подстрока может находиться в любом месте строки. Расширение pg_trgm разбивает текст на триграммы — последовательности из трех символов — и строит по ним индекс, который позволяет быстро находить совпадения.

• Создание индекса: После активации расширения (CREATE EXTENSION IF NOT EXISTS pg_trgm;) можно создать GIN-индекс (обычно он предпочтительнее для такого типа поиска) или GiST-индекс.

CREATE INDEX idx_users_name_trgm ON users USING GIN (name gin_trgm_ops);

Важное ограничение: Триграммы работают для шаблонов длиной от 3 символов. Поиск одно- или двухсимвольных подстрок не будет эффективно использовать этот индекс.

2. Тонкая настройка B-tree индексов для простых случаев

Если поиск осуществляется только по началу строки, стандартный B-tree индекс будет самым эффективным решением, так как он создает упорядоченную структуру, идеально подходящую для поиска по диапазону.

• Для ILIKE (регистронезависимый поиск) обычный B-tree индекс не подойдет. Вместо него нужно создать функциональный индекс на результат функции LOWER() или UPPER() и использовать эту же функцию в запросе.

CREATE INDEX idx_users_name_lower ON users (LOWER(name));

-- Использовать в запросе:

SELECT * FROM users WHERE LOWER(name) LIKE LOWER('ivan%');

Локаль базы данных: Если база данных использует не C локаль, для поддержки LIKE может потребоваться создать индекс с классом операторов text_pattern_ops.

Начало работ и детали запроса

Оптимизация SQL-запросов PostgreSQL : большое количество LEFT OUTER JOIN (пример)

Оптимизация запросов с большим количеством LEFT OUTER JOIN в PostgreSQL — это комплексная задача, которая требует подхода как к написанию запроса, так и к настройке самой СУБД. Ключевые направления — это помощь планировщику запросов, правильное индексирование и рассмотрение альтернативных архитектурных решений.

1.Управление порядком JOIN

Ключевая идея

Уменьшить количество вариантов плана для анализа планировщиком

Пример действий

Использовать явный синтаксис JOIN и настроить параметр join_collapse_limit.

2.Оптимизация структур данных

Ключевая идея

Снизить нагрузку на операции сравнения и хеширования.

Пример действий

Использовать более компактные типы данных (например, INT вместо TEXT для ID).

3.Эффективное индексирование

Ключевая идея

Обеспечить быстрое нахождение строк для соединения

Пример действий

Создать индексы на колонках, участвующих в условиях ON для каждого соединения.

4.Архитектурные изменения

Ключевая идея

Полностью избежать затрат на соединение во время выполнения запроса

Пример действий

Рассмотреть денормализацию таблиц или использование материализованных представлений.

💡 Практические шаги по оптимизации

1.Помочь планировщику запросов

Планировщик PostgreSQL при большом количестве JOINов сталкивается с экспоненциальным ростом числа возможных планов выполнения. Чтобы сократить время планирования, можно использовать явный синтаксис JOIN (например, a LEFT JOIN b ON ... LEFT JOIN c ON ...), который задает более предсказуемый порядок. Затем можно установить параметр join_collapse_limit = 1, чтобы планировщик следовал этому порядку. Это особенно актуально, если само построение плана запроса занимает значительное время (секунды).

2. Анализ и упрощение данные

Если в условиях JOIN используются текстовые поля большой длины, операции хеширования и сравнения могут стать "узким местом". По возможности стоит использовать более подходящие и компактные типы данных (например, целые числа для идентификаторов). Также необходимо убедиться, что типы данных связываемых колонок совпадают, чтобы избежать неявного преобразования типов.

3.Правильные индексы

Это основа основ. Для каждого условия ON в ваших LEFT JOIN должен существовать индекс. Как минимум, индексируются колонки из правой таблицы. Например, для ... LEFT JOIN table_b ON table_a.id = table_b.a_id ... полезно иметь индекс на table_b.a_id.

4.Альтернативные подходы

Если запрос выполняется редко, но требует много ресурсов, эффективным решением может быть материализованное представление (Materialized View), которое хранит результат запроса на диске и периодически обновляется.

Для часто меняющихся данных иногда оправдана денормализация — дублирование часто запрашиваемых колонок в одну таблицу, чтобы избежать JOIN.

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

Задача

Подготовить и протестировать на продуктивной СУБД, типовой шаблон действий при анализе причин инцидента производительности СУБД.

Шаблон

1. Стартовое событие - инцидент снижения производительности СУБД.

2. Корреляция типов ожиданий - определение типа ожидания с наибольшей корреляцией.

3. Диаграмма Парето по SQL запросам по данному типу ожидания - набор SQL запросов для последующей оптимизации.

1. Стартовое событие - инцидент снижения производительности СУБД

Zabbix

Результат - Стартовое событие для начала процессе Incident Management

2. Корреляция типов ожиданий - определение типа ожидания с наибольшей корреляцией

Источник:

Отчёт по статистике СУБД : make_summary_with_sql.sh

Результат - тип ожидания с наибольшим коэффициентом корреляции

Тип ожидания IPC имеет наибольший коэффициент корреляции между типом ожидания и ожиданиями СУБД.

3. Диаграмма Парето по SQL запросам по данному типу ожидания

Источник:

Отчёт по статистике СУБД : make_summary_with_sql.sh

Результат - SQL запросы имеющие 80% ожиданий типа IPC

• 6863414396188999698

• -5395258115281111645

• -4533756551948631336

• -4460774138492313959

Продолжение

Оптимизация SQL запроса -5395258115281111645

• Оптимизация SQL-запросов PostgreSQL : индекс по UUID

Оптимизация SQL запроса -4460774138492313959

• Оптимизация SQL-запросов PostgreSQL : COUNT(*)