Postgres Pro
Downloads
Home   >   mailing lists

Thread: bulk DELETE speed

bulk DELETE speed

From
"Kuhn, Dylan K (4520500D)"
Date:

I'm trying to figure out why deletes are slow on a particular table. 

I have a few tables with millions of rows, and have noticed that deleting rows is many times slower on one of them.  It does have PL/PGSQL before and after delete triggers.  I've tried disabling the triggers in pg_trigger, dropping them, doing the deletes in transactions, with exclusive locks -- nothing seems to work.  Maybe it isn't the triggers at all, and I need to do some sort of analysis on my table to figure out why it is slow?

Does anyone have any other tricks for speeding up bulk deletes? 

Thanks,
Dylan Kuhn

Re: bulk DELETE speed

From
"Marie G. Tuite"
Date:
If you can delete all records, try truncating the table:
 
truncate table your_table;
 
This is more efficient and also has the affect of resetting the table.
-----Original Message-----
From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql.org]On Behalf Of Kuhn, Dylan K (4520500D)
Sent: Friday, May 16, 2003 12:15 PM
To: pgsql-admin@postgresql.org
Subject: [ADMIN] bulk DELETE speed


I'm trying to figure out why deletes are slow on a particular table. 

I have a few tables with millions of rows, and have noticed that deleting rows is many times slower on one of them.  It does have PL/PGSQL before and after delete triggers.  I've tried disabling the triggers in pg_trigger, dropping them, doing the deletes in transactions, with exclusive locks -- nothing seems to work.  Maybe it isn't the triggers at all, and I need to do some sort of analysis on my table to figure out why it is slow?

Does anyone have any other tricks for speeding up bulk deletes? 

Thanks,
Dylan Kuhn

Re: bulk DELETE speed

From
"Kuhn, Dylan K (4520500D)"
Date:
I'm not deleting all the records, but I could maybe copy the ones I want to keep into a temporary table.  It looks like I have to somehow disable the foreign key constraints that reference the table before truncating it.  Is this possible?
-----Original Message-----
From: Marie G. Tuite [mailto:marie.tuite@edisonaffiliates.com]
Sent: Friday, May 16, 2003 11:16
To: Kuhn, Dylan K (4520500D); pgsql-admin@postgresql.org
Subject: RE: [ADMIN] bulk DELETE speed

If you can delete all records, try truncating the table:
 
truncate table your_table;
 
This is more efficient and also has the affect of resetting the table.
-----Original Message-----
From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql.org]On Behalf Of Kuhn, Dylan K (4520500D)
Sent: Friday, May 16, 2003 12:15 PM
To: pgsql-admin@postgresql.org
Subject: [ADMIN] bulk DELETE speed


I'm trying to figure out why deletes are slow on a particular table. 

I have a few tables with millions of rows, and have noticed that deleting rows is many times slower on one of them.  It does have PL/PGSQL before and after delete triggers.  I've tried disabling the triggers in pg_trigger, dropping them, doing the deletes in transactions, with exclusive locks -- nothing seems to work.  Maybe it isn't the triggers at all, and I need to do some sort of analysis on my table to figure out why it is slow?

Does anyone have any other tricks for speeding up bulk deletes? 

Thanks,
Dylan Kuhn

Re: bulk DELETE speed

From
"Victor Yegorov"
Date:
* Kuhn, Dylan K (4520500D) <Dylan.Kuhn@navy.mil> [16.05.2003 20:31]:
>
> I'm trying to figure out why deletes are slow on a particular table.
>
> I have a few tables with millions of rows, and have noticed that deleting rows is many times slower on one of them.
Itdoes have PL/PGSQL before and after delete triggers.  I've tried disabling the triggers in pg_trigger, dropping them,
doingthe deletes in transactions, with exclusive locks -- nothing seems to work.  Maybe it isn't the triggers at all,
andI need to do some sort of analysis on my table to figure out why it is slow? 
>
> Does anyone have any other tricks for speeding up bulk deletes?

A couple of days ago I've found the same problem.

Check, if your table has a primary key (guess it does), thatis referenced by
any other tables. If so, for each row you're deleteing, backend first is
checking for existance of related ones in other tables.

In my case, those "othertables" wasn't indexed by the foreign key field.
That means - for each row to delete backend is performing seq. scan on some
other table. Even if it isn't so big, it still slows down the things.

Look around, may be this will help.

--

Victor Yegorov

Re: bulk DELETE speed

From
Robert Treat
Date:
On Fri, 2003-05-16 at 15:07, Kuhn, Dylan K (4520500D) wrote:
> I'm not deleting all the records, but I could maybe copy the ones I want
> to keep into a temporary table.  It looks like I have to somehow disable
> the foreign key constraints that reference the table before truncating
> it.  Is this possible?
>

Syntax for 7.2.x is:

UPDATE "pg_class" SET "reltriggers" = 0 WHERE "relname" = 'mytable';
UPDATE pg_class SET reltriggers = (SELECT count(*) FROM pg_trigger where pg_class.oid = tgrelid) WHERE relname =
'mytable';

I think it's the same in 7.3.x, but double check before using it.

Robert Treat