Skip site navigation (1) Skip section navigation (2)

Re: Slow deletes in 8.1 when FKs are involved

From: Will Reese <wreese(at)rackspace(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Re: Slow deletes in 8.1 when FKs are involved
Date: 2006-04-26 00:02:37
Message-ID: 65B945E0-2F69-42F2-B594-74A680B25A3A@rackspace.com (view raw or flat)
Thread:
Lists: pgsql-performance
I did double check for indexes on the referenced and referencing  
columns, and even though this database is restored and vacuum  
analyzed nightly the issue remains.  Using explain analyze in  
postgresql 8.1, I was able to see where the problem lies.  For  
performance reasons on our 7.4 server, we removed one of the 3 RI  
triggers for some constraints (the RI trigger that performs the  
SELECT....FOR UPDATE to prevent modifications) and replaced it with a  
trigger to just prevent deletes on this data indefinitely (the data  
never gets deleted or updated in our app).  This works great in  
postgresql 7.4 and nearly eliminated our performance issue, but when  
that database is restored to postgresql 8.1 one of the remaining two  
RI triggers does not perform well at all when you try to delete from  
that table (even though it's fine in postgresql 7.4).  On the 8.1  
server I dropped the remaining two RI triggers, and added the  
constraint to recreate the 3 RI triggers.  After that the delete  
performed fine.  So it looks like the 7.4 RI triggers that carried  
over to the 8.1 server don't perform very well.  I'm hoping that the  
SELECT...FOR SHARE functionality in 8.1 will allow us to re-add our  
constraints and not suffer from the locking issues we had in  
postgresql 7.4.

Will Reese -- http://blog.rezra.com

On Apr 23, 2006, at 10:32 PM, Tom Lane wrote:

> Will Reese <wreese(at)rackspace(dot)com> writes:
>> ... Both servers have identical postgresql.conf settings and were
>> restored from the same 7.4 backup. Almost everything is faster on the
>> 8.1 server (mostly due to hardware), except one thing...deletes from
>> tables with many foreign keys pointing to them.
>
> I think it's unquestionable that you have a bad FK plan in use on the
> 8.1 server.  Double check that you have suitable indexes on the
> referencing (not referenced) columns, that you've ANALYZEd all the
> tables involved, and that you've started a fresh psql session  
> (remember
> the backend tends to cache FK plans for the life of the connection).
>
> It might help to EXPLAIN ANALYZE one of the slow deletes --- 8.1 will
> break out the time spent in FK triggers, which would let you see which
> one(s) are the culprit.
>
> 			regards, tom lane


In response to

pgsql-performance by date

Next:From: Junaili LieDate: 2006-04-26 00:14:56
Subject: Re: slow deletes on pgsql 7.4
Previous:From: Jim C. NasbyDate: 2006-04-25 23:55:04
Subject: Re: Large (8M) cache vs. dual-core CPUs

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group