Как оптимизировать выборку из БД?

Question

[Valeriu Vodnicear]

Может кто-то помочь оптимизировать вот этот запрос:

SELECT q.id
FROM questions.question q
WHERE
  NOT EXISTS (SELECT qt.question_id FROM questions.question_translate_it qt WHERE q.id = qt.question_id)
  AND
  NOT EXISTS (SELECT true FROM questions.translate_questions tq WHERE q.id = tq.question_id AND tq.language_id = 8)
LIMIT 1;

он работает, но долго
индексы:

• q.id - primary
  
• qt.question_id - unique
  
• tq.question_id, tq.language_id - unique
  

Вот план выполнения запроса:

Comments

[Сергей c0re]

а если сделать через left join ?

[alex-1917]

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

[Сергей c0re]

Так вроде:

SELECT q.id
  FROM questions.question q
  LEFT JOIN questions.question_translate_it qt ON q.id = qt.question_id
  LEFT JOIN questions.translate_questions tq ON q.id = tq.question_id AND tq.language_id = 8
  WHERE qt.question_id IS NULL
    AND tq.question_id IS NULL
  LIMIT 1
;

[Valeriu Vodnicear]

Сергей, спасибо конечно, но результат одинаковый

[Сергей c0re]

Valeriu Vodnicear, а если так?

SELECT q.id
  FROM questions.question q
  LEFT JOIN questions.question_translate_it qt ON q.id = qt.question_id
  LEFT JOIN (SELECT question_id FROM questions.translate_questions WHERE language_id = 8) tq ON q.id = tq.question_id
  WHERE qt.question_id IS NULL
    AND tq.question_id IS NULL
  LIMIT 1
;

[Сергей c0re]

Valeriu Vodnicear, время выполнения вообще никак не меняется??

[Сергей c0re]

Valeriu Vodnicear, сколько оперативы на сервере и сколько можно постгресу отдать?
может подкрутить праметры постгреса?

[Сергей c0re]

show shared_buffers;
show work_mem;
show maintenance_work_mem;
show effective_cache_size;

[Valeriu Vodnicear]

Сергей, да, время выполнения не меняеться
да, конфиги postgresql вообще не крутили

только что пробовал исходя из этого pgconfigurator.cybertec.at что-то поправить

оперативки на сервере 96GB, щас постгресу можно отдать примерно 35-40, но если этого мало, то можем ещё освободить оперативку для pg

Версия postgres:

PostgreSQL 9.2.24 on x86_64-redhat-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-28), 64-bit

после мойх конфигов:

• shared_buffers: 16GB
  
• work_mem: 128MB
  
• maintenance_work_mem: 520MB
  
• effective_cache_size: 30GB
  

[Сергей c0re]

Valeriu Vodnicear, а у вас много процессов работающих с Postgres ?
попробуйте по другому распределить память.

shared_buffers = 5GB                    # ~ 1/8 RAM
work_mem = 2GB                          # ~ 1/20 RAM
maintenance_work_mem = 10GB             # ~ 1/4 RAM
effective_cache_size = 23GB              # ~ 2/3 RAM

work_mem. Под каждый запрос можно выделить личный ограниченный объём памяти для работы. Этот объём может использоваться для сортировки, объединения и других подобных операций. При превышении этого объёма сервер начинает использовать временные файлы на диске, что может существенно замедлить скорость обработки запросов. Предел для work_mem можно вычислить, разделив объём доступной памяти (физическая память минус объём занятый под другие программы и под совместно используемые страницы shared_buffers) на максимальное число одновременно используемых активных соединений. При необходимости, например, выполнения очень объёмных операций, допустимый лимит можно изменять прямо во время выполнения запроса. Поэтому нет нужды изначально задавать теоретический предел.

попробуйте для начала перед запросом увеличить work_mem

SET work_mem = '2GB'; -- например
SELECT q.id
  FROM questions.question q
  LEFT JOIN questions.question_translate_it qt ON q.id = qt.question_id
  LEFT JOIN questions.translate_questions tq ON q.id = tq.question_id AND tq.language_id = 8
  WHERE qt.question_id IS NULL
    AND tq.question_id IS NULL
  LIMIT 1
;

[Сергей c0re]

ps:

• work_mem. Под каждый запрос можно выделить личный ограниченный объём памяти для работы. Этот объём может использоваться для сортировки, объединения и других подобных операций. При превышении этого объёма сервер начинает использовать временные файлы на диске, что может существенно замедлить скорость обработки запросов. Предел для work_mem можно вычислить, разделив объём доступной памяти (физическая память минус объём занятый под другие программы и под совместно используемые страницы shared_buffers) на максимальное число одновременно используемых активных соединений. При необходимости, например, выполнения очень объёмных операций, допустимый лимит можно изменять прямо во время выполнения запроса. Поэтому нет нужды изначально задавать теоретический предел.
  
• effective_cache_size Этот параметр сообщает PostgreSQL примерный объём файлового кэша операционной системы, оптимизатор использует эту оценку для построения плана каждого запроса. Объём задаётся параметром effective_cache_size в postgresql.conf. Единица измерения – блоки величиной 8 кБ. Например, пусть в вашем компьютере 1,5 ГБ памяти, параметр shared_buffers установлен в 32 МБ, а параметр effective_cache_size в 800 МБ. Если запросу нужно 700 МБ данных, то PostgreSQL оценит, что все нужные данные уже есть в памяти и выберет более агрессивный план с использованием индексов и merge joins. Но если effective_cache_size будет всего 200 МБ, то оптимизатор вполне может выбрать более эффективный для дисковой системы план, включающий полный просмотр таблицы. В качестве начального значения можете использовать 25-50% доступной памяти (т.е. не занятой операционной системой и приложениями). Этот параметр в ОС можно посмотреть в настройках: Для Windows: в Диспетчере задач, Закладка Быстродействие, Физическая память-Системный кэш. Для Linux: наберите команду free, необходимое значение в столбце cached (в kB) Данное значение необходимо разделить на количество конкурентных запросов в один момент времени (среднее количество подключений к базе + запас).
  
• maintenance_work_mem. Эта память используется для выполнения операций по сбору статистики (ANALYZE), сборке мусора (VACUUM), создания индексов (CREATE INDEX) и для добавления внешних ключей (FOREGIN KEY). Размер выделяемой под эти операции памяти должен быть сравним с физическим размером самого большого индекса на диске. Как и в случае work_mem эта переменная может быть установлена прямо во время выполнения запроса.
  
• max_prepared_transactions. Определяет максимальное число подготовленных транзакций (команда PREPARE TRANSACTION). Подготовленные транзакции выполняются, но результат их не будет доступен пока их не подтвердят (COMMIT). Так же можно такие транзакции и отклонить (ROLLBACK). Если эта сущность нигде не используется, то переменную можно занулить.
  

[Valeriu Vodnicear]

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

[Valeriu Vodnicear]

решили поменять немного архитектуру чтобы запрос был более лёгкий

и вроде получилось
ещё тестируем

спасибо Сергей за помощь

[Сергей c0re]

Valeriu Vodnicear,

в минуту добавляються по 5000

ну это вроде как не предел загрузки, всего-то 80-85 вставок в сек.

решили поменять немного архитектуру чтобы запрос был более лёгкий

да он вроде как и не особо тяжелый...
а сколько выполнялся вообще?
и пробовали ли увеличивать work_mem ?
просто 128MB, имхо, как-то малова-то.

[Valeriu Vodnicear]

Сергей, work_mem да увеличивали
он начал работать под нагрузку минут 10 и нагрузка постепенно растёт
сразу запрос тот выполнялся быстро
а после минут 15-20 интесивной работы сервера, запрос начал медленее и медленее работать

[Сергей c0re]

Valeriu Vodnicear, может еще и maintenance_work_mem увеличивать.

PS: то что там по дефолту выставляется на сервер, имхо полная шляпа, они там и пишут AS IS и никакой ответственности.

Answer 1

[Alex Wells]

SELECT q.id
FROM questions.question q
LEFT JOIN questions.question_translate_it qt ON q.id = qt.question_id
LEFT JOIN questions.translate_questions tq ON q.id = tq.question_id
  AND tq.language_id = 8
WHERE qt.question_id IS NULL
WHERE tq.question_id IS NULL;

Comments

[Сергей c0re]

o_O оригинально ...