From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> |
Cc: | Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com>, Jan Wieck <JanWieck(at)Yahoo(dot)com>, PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: Open 7.4 items |
Date: | 2003-10-05 23:39:59 |
Message-ID: | 15768.1065397199@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers pgsql-patches |
Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> Oh, that makes me feel better. Do we have timings for this code?
This is just a single data point, but I made a table of 1 million
rows containing just the int4 primary key column (values 0-1million
in a somewhat random order). Then I copied the same data, sans index,
to produce a foreign key table. Then I tried ALTER ADD PRIMARY KEY.
The results were:
Time to load the 1 million rows: 8 sec
Time to create the PK index: 10 sec
Time to ADD PRIMARY KEY:
with CVS-tip code (fire trigger per row): 78 sec
with proposed patch: anywhere from 5 to 25 sec depending on plan
The default plan if there is no index on the FK table (meaning the
planner will not know its true size) is a nestloop with inner index
scan taking about 17 sec.
If any index has been created on the FK table, you'll probably get a
merge or hash join. I found these took about 20 sec with the default
sort_mem setting, but with sort_mem boosted to 50000 or more, the
hash join got lots faster --- down in the 6-7 second range ---
presumably because it didn't need multiple hash batches.
It'd clearly be worth our while to mention boosting sort_mem as a
helpful thing to do during bulk data load --- it should speed up
btree index creation too. I don't think that tip appears anywhere
in the docs at the moment.
So the patch definitely seems worthwhile, but someone might still care
to argue that there should be a bypass switch available too.
regards, tom lane
From | Date | Subject | |
---|---|---|---|
Next Message | Bruce Momjian | 2003-10-05 23:51:55 | Re: Open 7.4 items |
Previous Message | Tom Lane | 2003-10-05 23:22:38 | Re: [COMMITTERS] pgsql-server/src/backend/catalog aclchk.c |
From | Date | Subject | |
---|---|---|---|
Next Message | Bruce Momjian | 2003-10-05 23:51:55 | Re: Open 7.4 items |
Previous Message | Tom Lane | 2003-10-05 23:12:50 | Re: Cannot dump/restore text value \N |