On Mon, 17 Nov 2003, ow wrote:
> --- Jeff <threshar@torgo.978.org> wrote:
> > On Mon, 17 Nov 2003 10:40:20 -0800 (PST)
> > Stephan Szabo <sszabo@megazone.bigpanda.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 assume you're also not modifying the pktable rows (since that would
access the fk table based solely on the fk). Does the multi-field index
start with fk or some other field? Hmm, the changes in 7.4 for alter
probably don't help as much without an index (it saves the cost of making
all those executors, but that's might be it). Doing, schema, turning off
triggers on the table, data might have worked in general, but if you've
got a textual combined dump that'd be a mess for a workaround (it might be
reasonable in pg_restore, but I don't tend to use it, so I couldn't say)
