Bruce Momjian <[EMAIL PROTECTED]> 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.
Does an ANALYZE run between index creation and bulk FK checking improve planning? It's not doing a full DB scan, so it shouldn't do too much harm, does it?
Jan
-- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== [EMAIL PROTECTED] #
---------------------------(end of broadcast)--------------------------- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
