PostgreSQL 7.4.2 ... The tables in question have been vacuumed.
...stepping gingerly into the woods of foreign keys... I need some advice:
Given a foriegn key structure:
table1.p1 uniqueidentifier
table2.p1 uniqueidentifier
table3.p1 uniqueidentifier
table4.p1 uniqueidentifier
table4.q1 uniqueidentifier
table5.q1 uniqueidentifier
...
CONSTRAINT table2_p1_fkey FOREIGN KEY (p1) REFERENCES table1 (p1) ON UPDATE
CASCADE ON DELETE CASCADE;
CONSTRAINT table3_p1_fkey FOREIGN KEY (p1) REFERENCES table1 (p1) ON UPDATE
CASCADE ON DELETE CASCADE;
CONSTRAINT table4_p1_fkey FOREIGN KEY (p1) REFERENCES table1 (p1) ON UPDATE
CASCADE ON DELETE CASCADE;
CONSTRAINT table5_q1_fkey FOREIGN KEY (q1) REFERENCES table4 (q1) ON UPDATE
CASCADE ON DELETE CASCADE;
I want to clean every one of those tables out with a "delete from table1;" ...
So, If I :
db=# explain delete from table1;
I get something like this:
QUERY PLAN
------------------------------------------------------------------
Seq Scan on table1 (cost=0.00..1073.80 rows=39780 width=6)
(1 row)
It would appear that the query would run as fast as the table could be scanned.
But the query takes so long, I've never let it finish! Of course, it is because
it has to cascade the delete... I never dreamed it would be so expensive.
I can improve my performance within the transaction by using INITIALLY DEFERRED
vs. INITIALLY IMMEDIATE, but all that heavy lifting is just put off until
COMMIT.
What can be done to increase the overall speed of this transaction, keeping the
FKey Constraints in-place?
CG
__________________________________
Do you Yahoo!?
Win a $20,000 Career Makeover at Yahoo! HotJobs
http://hotjobs.sweepstakes.yahoo.com/careermakeover
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
