From: Віталій Тимчишин <tivv00@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Delete performance again
Date: Thu, 2 Oct 2008 18:21:59 +0300
Message-ID: <331e40660810020821w4c4f212br3943df435589f731@mail.gmail.com> (raw)
In-Reply-To: <23315.1222949660@sss.pgh.pa.us>
References: <331e40660810020242u2f0f5de2qe146312e448a7df4@mail.gmail.com>
<23315.1222949660@sss.pgh.pa.us>
2008/10/2 Tom Lane <tgl@sss.pgh.pa.us>
> "=?ISO-8859-5?B?svbi0Nv22SDC2Nzn2OjY3Q==?=" <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".
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tivv00@gmail.com
Subject: Re: Delete performance again
In-Reply-To: <331e40660810020821w4c4f212br3943df435589f731@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox