Проверяет умение диагностировать и решать проблемы производительности PostgreSQL в Java-приложениях.
Добавить индекс CREATE INDEX idx_author_name ON authors(name), использовать покрывающие индексы (INCLUDE) для частых запросов, настроить частичные индексы если данные фильтруются, и переписать запросы для исключения LIKE '%...'. Для миллионов записей — рассмотреть полнотекстовый поиск.
Проблема: Полнотекстовый поиск (WHERE name LIKE '%Толстой%') без индекса сканирует всю таблицу.
Решение:
-- Базовый индекс (ускоряет точные совпадения и "starts with")
CREATE INDEX idx_author_name ON authors(name);
-- Для поиска по части строки (только для "starts with")
SELECT * FROM authors WHERE name LIKE 'Толстой%'; -- Использует индексLIKE с любым положениемПроблема: LIKE '%Толстой' не использует обычный индекс.
Решение:
-- Индекс для триграмм (расширение pg_trgm)
CREATE EXTENSION pg_trgm;
CREATE INDEX idx_author_name_trgm ON authors USING gin(name gin_trgm_ops);
-- Теперь запрос использует индекс:
SELECT * FROM authors WHERE name LIKE '%Толстой';Для частых запросов с дополнительными полями:
-- Индекс "покрывает" все данные запроса
CREATE INDEX idx_author_cover ON authors(name) INCLUDE (id, birth_date);Если данные фильтруются (например, только активные авторы):
CREATE INDEX idx_active_authors ON authors(name) WHERE is_active = true;Для миллионов записей и морфологического поиска:
ALTER TABLE authors ADD COLUMN name_tsvector tsvector;
UPDATE authors SET name_tsvector = to_tsvector('russian', name);
CREATE INDEX idx_fts_author ON authors USING gin(name_tsvector);
-- Поиск по словам в любом порядке и формах
SELECT * FROM authors
WHERE name_tsvector @@ to_tsquery('russian', 'Толстой');Используйте пагинацию и кэширование:
// Spring Data JPA + Пагинация
Page<Author> authors = authorRepository.findByNameContaining("Толстой", PageRequest.of(0, 20));
// Кэширование (Spring Cache)
@Cacheable("authors")
public List<Author> findAuthors(String name) {
return authorRepository.searchByName(name);
}Анализ запросов:
EXPLAIN ANALYZE SELECT * FROM authors WHERE name LIKE '%Толстой%';Настройка памяти: Увеличьте shared_buffers и work_mem в postgresql.conf.
