Re: [PERFORM] Adding foreign key performance

2003-10-29 Thread Jeff
On Wed, 29 Oct 2003 09:47:28 +0800 Christopher Kings-Lynne <[EMAIL PROTECTED]> wrote: > >>Just loaded up delicious 7.4b5 and wow... > >> > >>sort_mem 8192: 137038ms [lots of tmp file activity] > >>sort_mem 256000: 83109ms > > > 1. A full ANALYZE > 2. A delete all from pg_statistic > I had prev

Re: [PERFORM] Adding foreign key performance

2003-10-28 Thread Christopher Kings-Lynne
Just loaded up delicious 7.4b5 and wow... sort_mem 8192: 137038ms [lots of tmp file activity] sort_mem 256000: 83109ms Hmm, 298383 -> 83109 (since those are the 256k numbers). Not as much as I'd have hoped, but I'll take a factor of 3. Hi Jeff, Could you let us know the load times when you ha

Re: [PERFORM] Adding foreign key performance

2003-10-28 Thread Jeff
On Tue, 28 Oct 2003 14:22:04 -0500 Vivek Khera <[EMAIL PROTECTED]> wrote: > If you're restoring from a pg_dump -Fc (compressed dump) it already > happens for you. The indexes and foreign keys are not added until the > very end, from what I recall. > This happens with regular dumps - at the end

Re: [PERFORM] Adding foreign key performance

2003-10-28 Thread Bruce Momjian
John K. Herreshoff wrote: > That did not take long... about 13 minutes to reload the tables from an *.mdb > file, and a second or two for each of the 'alter table foo add foreign > key...' lines. I tried to drop a 'referencing' table, and the database would > not let me, said that something dep

Re: [PERFORM] Adding foreign key performance

2003-10-28 Thread Vivek Khera
> "J" == Jeff <[EMAIL PROTECTED]> writes: J> And this leads to the place we'd get a huge benefit: Restoring J> backups.. If there were some way to bump up sort_mem while doing J> the restore.. things would be much more pleasant. [Although, even There was a rather substantial thread on this a

Re: [PERFORM] Adding foreign key performance

2003-10-28 Thread John K. Herreshoff
That did not take long... about 13 minutes to reload the tables from an *.mdb file, and a second or two for each of the 'alter table foo add foreign key...' lines. I tried to drop a 'referencing' table, and the database would not let me, said that something depended on it ;o) Is there some way

Re: [PERFORM] Adding foreign key performance

2003-10-28 Thread Bruce Momjian
John K. Herreshoff wrote: > I'm not sure about the analyze stats... Where would I find that (in > postgresql.conf I suppose) I'll go see what I have set up, and get back to > you in 30 minutes or less... They are in pg_statistic. If you have ever anaylzed the table, there are stats. I am in

Re: [PERFORM] Adding foreign key performance

2003-10-28 Thread John K. Herreshoff
I'm not sure about the analyze stats... Where would I find that (in postgresql.conf I suppose) I'll go see what I have set up, and get back to you in 30 minutes or less... John. On Tuesday 28 October 2003 13:34, Bruce Momjian wrote: > John K. Herreshoff wrote: > > FWIW: I'm fiddling with th

Re: [PERFORM] Adding foreign key performance

2003-10-28 Thread Bruce Momjian
John K. Herreshoff wrote: > FWIW: I'm fiddling with that right now, and the FK think was quick... a few > seconds... the tables in question have 1400 records, 343000 records and 7200 > records... I'm running Beta5... Did those tables have analyze statistics? Can you try it without statistic

Re: [PERFORM] Adding foreign key performance

2003-10-28 Thread Jeff
On Tue, 28 Oct 2003 10:32:36 -0800 (PST) Stephan Szabo <[EMAIL PROTECTED]> wrote: > Hmm, 298383 -> 83109 (since those are the 256k numbers). Not as > much as I'd have hoped, but I'll take a factor of 3. Yes. those are the numbers for 256MB of sort_mem. It seemed to saturate the IO so once I get

Re: [PERFORM] Adding foreign key performance

2003-10-28 Thread Stephan Szabo
On Tue, 28 Oct 2003, Jeff wrote: > On Tue, 28 Oct 2003 09:16:45 -0500 > Jeff <[EMAIL PROTECTED]> wrote: > > > > 7.3.4: 328912ms [cpu pegged] > > 7.4b4: 298383ms [cpu pegged] > > > > Just loaded up delicious 7.4b5 and wow... > > sort_mem 8192: 137038ms [lots of tmp file activity] > sort_mem 256000

Re: [PERFORM] Adding foreign key performance

2003-10-28 Thread John K. Herreshoff
FWIW: I'm fiddling with that right now, and the FK think was quick... a few seconds... the tables in question have 1400 records, 343000 records and 7200 records... I'm running Beta5... John. On Tuesday 28 October 2003 10:21, Stephan Szabo wrote: > On Tue, 28 Oct 2003, Jeff wrote: > > I reca

Re: [PERFORM] Adding foreign key performance

2003-10-28 Thread Jeff
On Tue, 28 Oct 2003 09:16:45 -0500 Jeff <[EMAIL PROTECTED]> wrote: > 7.3.4: 328912ms [cpu pegged] > 7.4b4: 298383ms [cpu pegged] > Just loaded up delicious 7.4b5 and wow... sort_mem 8192: 137038ms [lots of tmp file activity] sort_mem 256000: 83109ms That's some good work there Lou, You'll

Re: [PERFORM] Adding foreign key performance

2003-10-28 Thread Tom Lane
Jeff <[EMAIL PROTECTED]> writes: > I recalled seeing a thread on -HACKERS about some major improvements to the speed of > adding an FK to an existing table in 7.4. Naturally I was curious and decided to > give it a whirl. My findings are not too good. In fact, they are bad. > 7.4b4, 10k shared b

Re: [PERFORM] Adding foreign key performance

2003-10-28 Thread Stephan Szabo
On Tue, 28 Oct 2003, Jeff wrote: > I recalled seeing a thread on -HACKERS about some major improvements to > the speed of adding an FK to an existing table in 7.4. Naturally I was > curious and decided to give it a whirl. My findings are not too good. In > fact, they are bad. > > Could it be this

[PERFORM] Adding foreign key performance

2003-10-28 Thread Jeff
I recalled seeing a thread on -HACKERS about some major improvements to the speed of adding an FK to an existing table in 7.4. Naturally I was curious and decided to give it a whirl. My findings are not too good. In fact, they are bad. Could it be this patch never made it in? Anyway, here's th