Message-ID: <445005AD.2000503@ummon.com>
Date: Wed, 26 Apr 2006 19:43:41 -0400
From: Stef T <stef@ummon.com>
User-Agent: Mail/News 1.5 (X11/20060309)
MIME-Version: 1.0
To: Will Reese <wreese@rackspace.com>
CC: pgsql-performance@postgresql.org
Subject: Re: Slow deletes in 8.1 when FKs are involved
References: <9EAB2F2F-0BC2-4685-96A1-A29D7872D068@rackspace.com>
In-Reply-To: <9EAB2F2F-0BC2-4685-96A1-A29D7872D068@rackspace.com>
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit


Hey there Will,
    I would assume that, perhaps, jst perhaps, the FK doesn't have an
index on the field on both sides, so, your seeing a potential sequential
scan happening. Can you fling up an explain anaylze for everyone please
? Anything more will be merely shooting in the dark, and, tracer bullets
aside, I have heard that -that- can be dangerous ;p

    Regards
    Stef

Will Reese wrote:
> 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 foreign keys.
>
> I have table A with around 100,000 rows, that has foreign keys to
> around 50 other tables.  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 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
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
>       subscribe-nomail command to majordomo@postgresql.org so that your
>       message can get through to the mailing list cleanly
>