Thread: Long-running DELETE

Long-running DELETE

From
jboes@nexcerpt.com (Jeff Boes)
Date:
Okay, this is really starting to fry me:

I'm trying to delete 1000 rows from a table which is referenced by
several different other tables as a foreign key.  I've turned on
debug_print_query to help track this down.  When I do deletes from
some tables, I can delete hundreds of rows per second.  When I delete
from the main table, it's about 1 row per second.  I've turned off
every trigger in the database:

update pg_triggers set tgenabled=false;

I read that such a change might require restarting the backend, so
I've gone so far as to bounce the database after this step.  What I
see in the log is

DEBUG:  query:  delete from a where id = 49;
DEBUG:  query: SELECT oid FROM ONLY "a_r" WHERE "id" = $1 FOR UPDATE
OF "a_r"
DEBUG:  query: SELECT oid FROM ONLY "a_c" WHERE "id" = $1 FOR UPDATE
OF "a_c"
DEBUG:  query: SELECT oid FROM ONLY "j" WHERE "id" = $1 FOR UPDATE OF
"j"
DEBUG:  query: SELECT oid FROM ONLY "a_r" WHERE "id" = $1 FOR UPDATE
OF "a_r"
DEBUG:  query: SELECT oid FROM ONLY "a_c" WHERE "id" = $1 FOR UPDATE
OF "a_c"
DEBUG:  query: SELECT oid FROM ONLY "j" WHERE "id" = $1 FOR UPDATE OF
"j"
DEBUG:  query: SELECT oid FROM ONLY "a_r" WHERE "id" = $1 FOR UPDATE
OF "a_r"
DEBUG:  query: SELECT oid FROM ONLY "a_c" WHERE "id" = $1 FOR UPDATE
OF "a_c"
DEBUG:  query: SELECT oid FROM ONLY "j" WHERE "id" = $1 FOR UPDATE OF
"j"
DEBUG:  query:  delete from a where id = 50;
DEBUG:  query:  delete from a where id = 53;
etc.


Re: Long-running DELETE

From
Stephan Szabo
Date:
On 15 Jan 2002, Jeff Boes wrote:

> Okay, this is really starting to fry me:
>
> I'm trying to delete 1000 rows from a table which is referenced by
> several different other tables as a foreign key.  I've turned on
> debug_print_query to help track this down.  When I do deletes from
> some tables, I can delete hundreds of rows per second.  When I delete
> from the main table, it's about 1 row per second.  I've turned off
> every trigger in the database:
>
> update pg_triggers set tgenabled=false;

I don't think tgenabled works.  You'll probably need to set reltriggers to
0 on the pg_class row for the table (and then reset it to the correct
value afterwards).




Re: Long-running DELETE

From
Tom Lane
Date:
jboes@nexcerpt.com (Jeff Boes) writes:
> I've turned off every trigger in the database:

> update pg_triggers set tgenabled=false;

AFAIR, tgenabled isn't really supported; it's not tested for AFTER
triggers, which is what foreign keys use.  Your debug trace certainly
shows that the FK triggers are still live.

pg_dump knows how to disable triggers for real; I think it involves
setting pg_class.reltriggers to zero.
        regards, tom lane


Re: Long-running DELETE

From
Jeff Boes
Date:
On Tue, 15 Jan 2002 21:13:49 -0800 (PST)
Stephan Szabo <sszabo@megazone23.bigpanda.com> wrote:

> 
> On 15 Jan 2002, Jeff Boes wrote:
> 
> > update pg_triggers set tgenabled=false;
> 
> I don't think tgenabled works.  You'll probably need to set reltriggers to
> 0 on the pg_class row for the table (and then reset it to the correct
> value afterwards).
> 

I can't confirm that one, but I did find that things got a lot faster
when I also did
 update pg_trigger  set tgenabled=false,   tgdeferrable=true,   tginitdeferred=true

for each of the associated triggers.

-- 
Jeff Boes                                             vox 616.226.9550
Database Engineer                                     fax 616.349.9076
Nexcerpt, Inc.                                      jboes@nexcerpt.com


Re: Long-running DELETE

From
Stephan Szabo
Date:
On Wed, 16 Jan 2002, Jeff Boes wrote:

> On Tue, 15 Jan 2002 21:13:49 -0800 (PST)
> Stephan Szabo <sszabo@megazone23.bigpanda.com> wrote:
>
> >
> > On 15 Jan 2002, Jeff Boes wrote:
> >
> > > update pg_triggers set tgenabled=false;
> >
> > I don't think tgenabled works.  You'll probably need to set reltriggers to
> > 0 on the pg_class row for the table (and then reset it to the correct
> > value afterwards).
> >
>
> I can't confirm that one, but I did find that things got a lot faster
> when I also did
>
>   update pg_trigger
>   set tgenabled=false,
>     tgdeferrable=true,
>     tginitdeferred=true
>
> for each of the associated triggers.

Hmm, that'd make the actual trigger calls wait till end of transaction.
If you were running the deletes in a transaction, that'd make the delete
parts go faster, but the commit should take a while.