Здравствуйте
Есть запрос
DELETE FROM table WHERE id IN (SELECT id FROM table WHERE site_id = $1 ORDER BY id limit $2)
В таблице есть индекс по site_id. Обычный план это скан индекса с сортировкой, но иногда сваливается в то, что ниже(из лога) и запрос выполняется по нескольку часов.

LOG: duration: 10841702.434 ms plan:
Query Text: DELETE FROM table WHERE id IN (SELECT id FROM table WHERE site_id = $1 ORDER BY id limit $2)
Delete on table (cost=515570.30..521686.55 rows=34596386 width=38)
-> Nested Loop (cost=515570.30..521686.55 rows=34596386 width=38)
-> HashAggregate (cost=515570.30..515572.30 rows=200 width=40)
-> Subquery Scan on "ANY_subquery" (cost=0.00..515567.83 rows=988 width=40)
-> Limit (cost=0.00..515557.95 rows=988 width=8)
-> Index Scan using pk_table on table (cost=0.00..119150243.09 rows=228336 width=8)
Filter: (site_id = 16079::bigint)
-> Index Scan using pk_table on table (cost=0.00..30.56 rows=1 width=14)
Index Cond: (id = "ANY_subquery".id)

Помогите разобраться в причине такого поведения

лимит в delete нужен, когда необходимо удалить лишнее.
в статистике 66992247 записей.
34 млн не может удалиться. Параметры запроса: $1 = '16079', $2 = '988'

LeXa NalBatсоздайте индексы по

(site_id, id) - для WHERE site_id = $1 ORDER BY id
(id) - для WHERE id IN
есть такой индекс. (id) для ORDER BY id, чтобы убрать сортировку, но оптимизатор его не использует. Точнее он его не использует на этой бд(есть еще 4 сервера, там этот индекс работает)

qwwq_Monah_пропущено...

есть такой индекс. (id) для ORDER BY id, чтобы убрать сортировку, но оптимизатор его не использует. Точнее он его не использует на этой бд(есть еще 4 сервера, там этот индекс работает)

так какой индекс у вас есть "для WHERE site_id = $1 ORDER BY id" ?
есть 2 индекса на сегодняшний день (site_id) и (site_id,id). Сори, если запутал

LeXa NalBat,
по pk по id. Судя по плану невозможно увидеть все индексы, которые есть на таблице.
Вопрос как раз и состоит в том, почему оптимизатор выбирает такой план и как ему помочь выбрать нужный.

ЗЫ Версия постгреса 9.1.8

LeXa NalBat,


Мда, плохо создался.
Попробую пересоздать

Индекс так и не создался, зато отработал вакуум и план вернулся на место.
Всем спасибо