Задача

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

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

3. Провести семантический анализ SQL запросов.

1. Семантический анализ рекомендаций по оптимизации ожиданий СУБД

Анализируя текст об оптимизации ожиданий DataFileRead в PostgreSQL, можно выделить следующее семантическое ядро:

Центральная концепция

"Сокращение дисковых операций через эффективное использование памяти и оптимизацию доступа к данным"

Ключевые смысловые кластеры

1. Управление памятью и кэшированием

-- Ключевые параметры

shared_buffers -- кэширование данных

work_mem -- операции в памяти

Семантика: Предотвращение физического чтения с диска через эффективное кэширование

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

• Индексное сканирование vs Последовательное сканирование

• Партиционирование больших таблиц

• Переиндексация для борьбы с раздуванием

Семантика: Минимизация объема читаемых данных

3. Проактивное обслуживание

autovacuum -- автоматическая очистка

VACUUM -- ручная очистка

ANALYZE -- обновление статистики

REINDEX -- перестройка индексов

Семантика: Предотвращение деградации производительности

4. Архитектурное масштабирование

• Вертикальное масштабирование: больше RAM, быстрые диски

• Горизонтальное масштабирование: горячие реплики для read-нагрузки

Семантика: Распределение нагрузки и увеличение ресурсов

Семантические связи и иерархия

Причинно-следственные связи

Недостаток памяти → Дисковые операции → DataFileRead ожидания

Отсутствие индексов → Sequential Scans → Увеличение I/O

Нет обслуживания → Bloat → Неэффективные чтения

Таксономия решений

Проактивные (предотвращение):

├── Настройка памяти

├── Создание индексов

└── Партиционирование

Реактивные (исправление):

├── Переиндексация

├── Агрессивный autovacuum

└── Оптимизация запросов

Ключевые метрики и показатели

• Эффективность кэширования (hit ratio)

• Объем последовательных сканирований

• Уровень раздувания индексов и таблиц

• Частота операций autovacuum

Семантические паттерны оптимизации

Паттерн "Память вместо диска"

-- До: операции на диске

-- После: операции в памяти

work_mem = 4MB → work_mem = 64MB

Паттерн "Точечный доступ вместо полного сканирования"

-- До: SELECT * FROM large_table WHERE condition

-- После: CREATE INDEX idx_condition ON large_table(condition)

Паттерн "Разделяй и властвуй"

-- Партиционирование больших таблиц по датам/диапазонам

CREATE TABLE data_2024 PARTITION OF main_table FOR VALUES FROM...

Выводы о семантическом ядре

Текст построен вокруг трех фундаментальных принципов:

1. Профилактика - настройка памяти и индексов до возникновения проблем

2. Эффективность - минимизация избыточных операций ввода-вывода

3. Регулярность - систематическое обслуживание для поддержания производительности

Главная семантическая ось: Память → Кэш → Диск с акцентом на смещении операций влево по этой оси для максимальной производительности.

2. Ключевые паттерны рекомендаций по оптимизации событий ожидания по заданному типу ожидания.

1. Общие причины проблем производительности

Конкуренция за ресурсы

• BufferContent: "конкуренция процессов за доступ к одним и тем же данным"

• BufferMapping: "конкуренция за доступ к буферному кэшу"

• LockManager: "общая проблема с большим количеством конкурентных блокировок"

• ParallelHashJoin: "процессы координируют свою работу или ожидают друг друга"

Проблемы с памятью

• BufferContent: "неэффективная работа с памятью", "данные не находятся в памяти"

• BufferMapping: "частота замены буферов и операций ввода-вывода"

• ParallelHashJoin: "нехватка оперативной памяти для построения хэш-таблиц"

2. Общие подходы к оптимизации

Настройка памяти

-- Общие параметры памяти :

BufferContent: shared_buffers

BufferMapping: shared_buffers

ParallelHashJoin: work_mem, hash_mem_multiplier

Борьба с раздуванием (Bloat)

• BufferContent: "Боритесь с раздуванием", "VACUUM FULL", "pg_repack", "REINDEX"

• BufferMapping: "Бороться с раздутостью (Bloat) таблиц и индексов"

• ParallelHashJoin: "раздутые (bloated) индексы", "VACUUM и ANALYZE"

Оптимизация индексов

• BufferContent: "Удалите неиспользуемые и дублирующиеся индексы", "Используйте частичные индексы"

• BufferMapping: "оптимизировать индексы"

• ParallelHashJoin: "Проверьте наличие адекватных индексов"

3. Общие методы диагностики

Мониторинг метрик

• BufferContent: "Следите за метрикой BufferCacheHitRatio"

• BufferMapping: "Мониторить blks_hit и blks_read"

• ParallelHashJoin: "EXPLAIN ANALYZE", "EXPLAIN (ANALYZE, BUFFERS)"

Анализ рабочей нагрузки

• Все тексты: необходимость анализа выполняемых запросов и выявления проблемных паттернов

4. Общие архитектурные рекомендации

Масштабирование ресурсов

• BufferContent: "Масштабируйте вычислительные ресурсы", "увеличение размера экземпляра БД"

• ParallelHashJoin: "Контролируйте уровень параллелизма", "max_parallel_workers"

Оптимизация приложения

• BufferContent: "пересмотреть логику приложения" для "горячих" данных

• LockManager: "пересмотр логики блокировок в приложении"

• ParallelHashJoin: "пересмотрите необходимость параллелизма для конкретных запросов"

5. Общие философские принципы

Комплексный подход

• Все тексты подчеркивают необходимость комплексного решения, а не точечных исправлений

Баланс ресурсов

• ParallelHashJoin: "Помните о балансе: Увеличивая лимиты памяти для одного запроса, вы можете уменьшить доступную память для других"

• Эта идея имплицитно присутствует во всех текстах

Смысловые совпадения и выводы

1. Память как ключевой ресурс - все проблемы так или иначе связаны с эффективным использованием памяти

2. Проактивное обслуживание - необходимость регулярного VACUUM, ANALYZE и мониторинга

3. Архитектурный подход - оптимизация требует изменений на всех уровнях: от параметров БД до логики приложения

4. Диагностика прежде оптимизации - везде подчеркивается важность анализа перед внесением изменений

3. Cемантический анализ SQL запросов.

1. Структурные паттерны

Паттерн мягкого удаления (Soft Delete)

-- Во всех запросах присутствует:

"deleteDateTime" IS NULL

• Запрос 1,2: "SuccessionPlan"."deleteDateTime" IS NULL, "successors"."deleteDateTime" IS NULL

• Запрос 3: "ImprovementPlanGoalToCourse"."deleteDateTime" IS NULL

Паттерн JOIN с дополнительными условиями

-- LEFT JOIN с фильтрацией в условии соединения

LEFT OUTER JOIN ... ON ... AND ("successors"."deleteDateTime" IS NULL AND ...)

2. Паттерны проекции данных

Селекция конкретных полей с алиасами

-- Паттерн: table."column" AS "alias.table.column"

"riskLeaving"."id" AS "riskLeaving.id"

"successors"."workflowStatusId" AS "successors.workflowStatusId"

Вложенные связи через JOIN

-- Цепочка связей между таблицами

"goal->improvementPlan"."empCodeId" AS "goal.improvementPlan.empCodeId"

3. Паттерны фильтрации

Фильтрация по идентификаторам

-- IN с списками значений

"SuccessionPlan"."positionId" IN (871798)

"SuccessionPlan"."positionId" IN (80214, 81665, 80217, ...)

Исключающая фильтрация

-- NOT IN для исключения статусов

"successors"."workflowStatusId" NOT IN (65, 66)

4. Паттерны бизнес-логики

Управление состоянием через workflowStatusId

• Во всех запросах присутствует работа со статусами (workflowStatusId, candidateStatusId)

Архивация и временные метки

-- Проверка на архивные записи

"SuccessionPlan"."archiveDate" IS NULL

Пагинация и упорядочивание

-- Запрос 3: ORDER BY ... LIMIT

ORDER BY "ImprovementPlanGoalToCourse"."id" ASC LIMIT 200

5. Смысловые совпадения по доменной модели

Управление кадровыми процессами

• Преемственность: successionPlan, successor, readinessId

• Развитие сотрудников: ImprovementPlanGoalToCourse, courseSession

• Оценка компетенций: participationResult, score, passed

Workflow и статусы

-- Общая система статусов

workflowStatusId, candidateStatusId, readinessId

applicantListType, participation_result

Временные жизненные циклы

• recordingDateTime, sessionBeginDate, sessionEndDate, update_date_time

6. Архитектурные паттерны

Нормализация данных

• Множество связанных таблиц с внешними ключами

• Разделение сущностей на основные и справочные

Гибкая система расширений

• Использование типа applicantListType

• Поддержка миграций через migration_code

Выводы и рекомендации

1. Единая архитектура: Все запросы следуют одним принципам проектирования

2. Консистентность: Единый подход к мягкому удалению и архивации

3. Масштабируемость: Использование пагинации для больших наборов данных

4. Бизнес-логика: Система ориентирована на управление HR-процессами

5. Оптимизационные возможности:
   Индексы по deleteDateTime, workflowStatusId
   Составные индексы для частых JOIN условий
   Оптимизация запросов с IN для больших списков

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

Задача

Экспериментальная проверка материалов доклада "Особенности записи WAL" PGConf.СПб 2025

Евгений Александров Т-Банк Старший инженер

Исследование механизма записи WAL в PostgreSQL с акцентом влияния на дисковую систему при высокой OLTP нагрузке. В докладе рассматриваются инструменты диагностики и даются рекомендации по настройке параметров, влияющих на поведение записи WAL.

Особенности записи WAL

Конфигурация тестовой ВМ

CPU = 8

RAM = 8GB

OS: RED OS MUROM (7.3.4)

PostgreSQL :

Postgres Pro (enterprise certified) 17.5.1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 11.4.1 20230605 (Red Soft 11.4.0-1), 64-bit

Эксперимент-1

commit_delay = 0

Эксперимент-2

commit_delay = 1000

Сценарий тестирования и нагрузка на СУБД

Mix

1. Select only : 50% нагрузки

2. Select + Update : 30% нагрузки

3. Insert only : 15% нагрузки

Нагрузка

Операционная скорость

Результаты

Среднее увеличение операционной скорости в эксперименте-2 составило 13.82%

Ожидания СУБД

Результаты

Среднее уменьшение ожиданий в эксперименте-2 составило ~1%

События ожидания (диаграмма Парето:80%)

Результат

1. Доля ожиданий Lock/relation в эксперименте-2 кардинально увеличилась.

2. Доля ожиданий LWLock/CheckpointerComm в эксперименте-2 кардинально увеличилась.

Показатели производительности инфраструктуры

iostat - для файловой системы /data

iostat - для файловой системы /wal

Утилизация диска файловой системы /wal

Результаты

Среднее уменьшение утилизации дискового устройства для файловой системы /wal в эксперименте-2 составило ~10%

Чек-лист CPU

Самый главный результат эксперимента

⚠️Рекомендация DeepSeek экспериментально не подтвердилась⚠️

-------------------------------------------------------------------------------------

commit_delay

Для конфигурации PostgreSQL 8 CPU, 8 GB RAM с shared_buffers=2GB под нагрузкой pgbench (5→100 соединений) оптимальное значение commit_delay = 0 (по умолчанию) является наиболее эффективным. Обоснование:

⚠️ Почему НЕ рекомендуется изменять commit_delay?

1. Природа pgbench нагрузки:
   TPC-B-like транзакции выполняются за 1-5 мс. Любая задержка коммита (commit_delay > 0) увеличивает latency на 20-100% .
   При 100 соединениях группировка коммитов не даёт выигрыша: короткие транзакции редко совпадают во времени .

Рекомендации DeepSeek по тюнингу конфигурационных параметров СУБД PostgreSQL

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

Стратегическая цель - интеграция 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` для больших таблиц

В подавляющем большинстве случаев современный планировщик PostgreSQL обработает условия в JOIN ... ON и в WHERE абсолютно идентично. Оба условия будут участвовать в формировании плана выполнения, и никакой разницы в производительности не будет.

Однако, есть несколько важных нюансов и сценариев, где перенос условий из ON в WHERE может быть полезен или, наоборот, критически важен.

1. Читаемость и однозначность запроса (Самый главный положительный эффект)

Это не прямое последствие для СУБД, а скорее для разработчика, но оно косвенно влияет на качество кода и меньшее количество ошибок.

• WHERE — это финальное фильтрующее условие. Оно четко указывает, какие строки должны попасть в окончательный результат.

• ON — это условие связи таблиц. Оно определяет, как строки двух таблиц соотносятся друг с другом.

Разделяя логику связи (ON) и логику фильтрации (WHERE), вы делаете запрос более понятным и легким для поддержки.

Пример:
Предположим, нам нужны все заказы и информация о клиентах, но только для клиентов из Москвы.

-- Менее читаемо (условие связи и фильтрации перемешаны)

SELECT *

FROM orders o

LEFT JOIN customers c ON o.customer_id = c.id AND c.city = 'Москва';

-- Более читаемо и логично

SELECT *

FROM orders o

LEFT JOIN customers c ON o.customer_id = c.id

WHERE c.city = 'Москва'; -- Но ВНИМАНИЕ! Этот запрос не эквивалентен предыдущему!

Здесь ключевой момент: Эти два запроса не эквивалентны. Второй запрос превратит LEFT JOIN в INNER JOIN, потому что условие на таблицу customers в WHERE отфильтрует все строки, где c.city IS NULL (т.е. все "не совпавшие" строки от LEFT JOIN).

2. Потенциальная помощь планировщику в сложных запросах

Хотя планировщик очень умный, в исключительно сложных запросах с множеством JOIN и подзапросов, явное указание финального условия отбора в WHERE может упростить процесс построения плана. Это не гарантированное ускорение, но упрощение логики для планировщика.

• Что делает планировщик? Он пытается "протолкнуть" условия (push down predicates) как можно ближе к сканированию данных.

• Разница: Условие в ON должно быть применено во время операции соединения, а условие в WHERE — после него. Иногда явное разделение позволяет планировщику применить фильтр на более раннем этапе, если это выгодно.

На практике эта разница почти всегда нивелируется мощностью планировщика PostgreSQL.

3. Явное указание логики для OUTER JOIN (самая важная практическая разница)

Это обратная сторона медали. Перенос условия из ON в WHERE для LEFT|RIGHT|FULL JOIN кардинально меняет результат запроса.

• Условие в ON: Фильтрует строки из правой таблицы до того, как будет выполнен JOIN. Строки из левой таблицы остаются, даже если условие не выполнилось.

• Условие в WHERE: Фильтрует результат после того, как JOIN был выполнен. Для LEFT JOIN это означает, что все строки, где правое поле — NULL (из-за несовпадения), будут отброшены, превращая join по сути во INNER.

Положительным последствием здесь является то, что использование WHERE позволяет вам явно и однозначно указать, что вы хотите видеть только пересекающиеся строки.

-- Хочу всех клиентов и их заказы, но только те заказы, которые > 1000

SELECT *

FROM customers c

LEFT JOIN orders o ON c.id = o.customer_id AND o.amount > 1000;

-- Вернет ВСЕХ клиентов. Для тех, у кого нет больших заказов, поля заказа будут NULL.

-- Хочу только тех клиентов, у КОТОРЫХ ЕСТЬ заказы > 1000

SELECT *

FROM customers c

LEFT JOIN orders o ON c.id = o.customer_id

WHERE o.amount > 1000;

-- Это, по факту, INNER JOIN по условию на заказ. Не вернет клиентов без подходящих заказов.

Выводы

1. Для INNER JOIN смело переносите условия из ON в WHERE. Разницы в производительности не будет, но запрос станет чище и понятнее. Планировщик обработает их одинаково.

2. Для OUTER JOIN (LEFT, RIGHT, FULL) понимайте разницу. Перенос условия из ON в WHERE полностью меняет логику запроса. Это не оптимизация, а изменение результата.
   ON: "Как соединять таблицы?"
   WHERE: "Что показывать в финальном результате?"

3. Положительные последствия переноса в WHERE — это в первую очередь:
   Улучшенная читаемость и сопровождаемость кода.
   Более явное и четкое выражение вашего намерения.
   В редких сложных случаях — потенциально более простой план запроса для СУБД.

Таким образом, главный положительный эффект — смысловой и архитектурный, а не прямой прирост производительности в современных версиях PostgreSQL.

Задача

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

Стратегическая цель - интеграция 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.