From: Віталій Тимчишин <tivv00@gmail.com>
To: pgsql-performance@postgresql.org
Subject: Delete performance again
Date: Thu, 2 Oct 2008 12:42:15 +0300
Message-ID: <331e40660810020242u2f0f5de2qe146312e448a7df4@mail.gmail.com> (raw)
Hello.
I have a database with company table that have a bunch of related
(delete=cascade) tables.
Also it has 1<->M relation to company_descr table.
Once we've found that ~half of our companies do not have any description and
we would like to remove them.
First this I've tried was
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. To make it faster I've done next thing:
create temporary table comprm(id) as select id from company;
delete from comprm where id in (select company_id from company_descr);
delete from company where id in (select id from comprm);
That was much better. So the question is why postgresql can't do such a
thing.
But it was better only until "removing" dataset was small (~5% of all
table).
As soon as I've tried to remove 50% I've got speed problems. I've ensured I
have all indexes for both ends of foreign key.
I've tried to remove all cascaded entries by myself, e.g.:
create temporary table comprm(id) as select id from company;
delete from comprm where id in (select company_id from company_descr);
delete from company_alias where company_id in (select id from comprm);
...
delete from company where id in (select id from comprm);
It did not help until I drop all constraints before and recreate all
constraints after.
Now I have it work for 15minutes, while previously it could not do in a day.
Is it OK? I'd say, some (if not all) of the optimizations could be done by
postgresql optimizer.
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: <331e40660810020242u2f0f5de2qe146312e448a7df4@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