Re: Open 7.4 items

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