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

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: Slow deletes in 8.1 when FKs are involved
Date: 2006-04-24 02:41:14
Message-ID: 22780344-3C6A-474E-8101-60B8806DA152@rackspace.com (view raw or flat)
Thread:
Lists: pgsql-performance
I'm preparing for an upgrade from PostgreSQL 7.4.5 to 8.1.3, and I  
noticed a potential performance issue.

I have two servers, a dual proc Dell with raid 5 running PostgreSQL  
7.4, and a quad proc Dell with a storage array running PostgreSQL  
8.1. 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 have table A with around 100,000 rows, that has foreign keys from  
about 50 other tables pointing to it.  Some of these other tables  
(table B, for example) have around 10 million rows.

On the 7.4 server, I can delete a single row from a table A in well  
under a second (as expected).  On the 8.1 server, it takes over a  
minute to delete.  I tried all the usual stuff, recreating indexes,  
vacuum analyzing, explain analyze.  Everything is identical between  
the systems.  If I hit ctrl-c while the slow delete was running on  
8.1, I repeatedly got the following message...

db=# delete from "A" where "ID" in ('6');
Cancel request sent
ERROR:  canceling statement due to user request
CONTEXT:  SQL statement "SELECT 1 FROM ONLY "public"."B" x WHERE  
"A_ID" = $1 FOR SHARE OF x"

It looks to me like the "SELECT ... FOR SHARE" functionality in 8.1  
is the culprit. Has anyone else run into this issue?


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


Responses

pgsql-performance by date

Next:From: Tom LaneDate: 2006-04-24 03:32:39
Subject: Re: Slow deletes in 8.1 when FKs are involved
Previous:From: Mark KirkwoodDate: 2006-04-24 01:29:17
Subject: Re: Hardware: HP StorageWorks MSA 1500

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