Обсуждение: Slow deletes in 8.1 when FKs are involved
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
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
>
Stef: There is already a post explaining the solution. All the proper indexes were there, and it works great on 7.4. The problem lies with leftover 7.4 RI triggers being carried over to an 8.1 database. The solution is to drop the triggers and add the constraint. Hopefully this will not cause as many locking issues with FKs on 8.1 as it did in 7.4 (which is why one of the RI triggers was removed in the first place). Will Reese -- http://blog.rezra.com On Apr 26, 2006, at 6:43 PM, Stef T wrote: > > 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 >> >