From: Stef T <stef@ummon.com>
To: Will Reese <wreese@rackspace.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Slow deletes in 8.1 when FKs are involved
Date: Wed, 26 Apr 2006 19:43:41 -0400
Message-ID: <445005AD.2000503@ummon.com> (raw)
In-Reply-To: <9EAB2F2F-0BC2-4685-96A1-A29D7872D068@rackspace.com>
References: <9EAB2F2F-0BC2-4685-96A1-A29D7872D068@rackspace.com>
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
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: stef@ummon.com, wreese@rackspace.com
Subject: Re: Slow deletes in 8.1 when FKs are involved
In-Reply-To: <445005AD.2000503@ummon.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox