Bruce Momjian <pgman@candle.pha.pa.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