Re: Delete performance again
| От | Віталій Тимчишин |
|---|---|
| Тема | Re: Delete performance again |
| Дата | |
| Msg-id | 331e40660810020821w4c4f212br3943df435589f731@mail.gmail.com обсуждение исходный текст |
| Ответ на | Re: Delete performance again (Tom Lane <tgl@sss.pgh.pa.us>) |
| Ответы |
Re: Delete performance again
|
| Список | pgsql-performance |
2008/10/2 Tom Lane <tgl@sss.pgh.pa.us>
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".
"Віталій Тимчишин" <tivv00@gmail.com> writes:Usually the reason for that is having forgotten to make an index on the
> 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.
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".
В списке pgsql-performance по дате отправления: