"Віталій Тимчишин" <tivv00@gmail.com> writes: > delete from company where id not in (select company_id from company_descr); > I've tried to analyze command, but unlike to other RDBM I've used it did not > include cascade deletes/checks into query plan. That is first problem. > It was SLOW.
Usually the reason for that is having forgotten to make an index on the referencing column(s) ?
Not at all. As you can see below in original message, simply "extending" the query to what should have been done by optimizer helps. I'd say optimizer always uses fixed plan not taking into account that this is massive update and id doing index lookup of children records for each parent record, while it would be much more effective to perform removal of all children records in single table scan.
It's like trigger "for each record" instead of "for each statement".