--- Jeff <[EMAIL PROTECTED]> wrote: > On Mon, 17 Nov 2003 10:40:20 -0800 (PST) > Stephan Szabo <[EMAIL PROTECTED]> wrote: > > > > > By the way, what does your schema look like? I created an 80M row fk > > table and 20K row pk table with an int4 key between them and indexes on > > the two key fields. It took about 25 minutes on my not terribly fast > > system using 7.4b5 to make the foreign key between them. It might have > > been faster if I'd raised sort_mem to something larger than 8192. > >
PK and FK keys are of "Dkey" domain, "Dkey" domain is mapped to INT4. There's PK index on 20K pk table, *NO* index on the fk field on 80M fk table. Why no index on the fk field? Several reasons: (1) app logic does not call for accessing fk table based solely on the fk (2) fk field is a part of multi-field AK index (3) unnecessary indexes take space and slow down inserts/updates, which is a factor for large tables. > i missed the start of this thread... but unless you are running 7.4 adding an > FK (restore) is very slow. ZIt is fixed in 7.4.. especially w/lots of sort > mem. Am running 7.4.rc2 and it takes me about 4.5 hours to verify one (1) FK constraint. > But the need still exists for a faster backup & restore. Can't agree more. Thanks __________________________________ Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard http://antispam.yahoo.com/whatsnewfree ---------------------------(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
