Re: Delete performance again

2008/10/2 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>

> "=?ISO-8859-5?B?svbi0Nv22SDC2Nzn2OjY3Q==?=" <tivv00(at)gmail(dot)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".