Re: Slow deletes in 8.1 when FKs are involved

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(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly
>