Tom Lane wrote:
> 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.
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. #
#================================================== JanWieck@Yahoo.com #
