Re: Slow deletes in 8.1 when FKs are involved

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@rackspace.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