Re: pg_restore and FK constraints with large dbs

--- Jeff <threshar(at)torgo(dot)978(dot)org> wrote:
> On Mon, 17 Nov 2003 10:40:20 -0800 (PST)
> Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com> 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