Re: Open 7.4 items - Mailing list pgsql-hackers

From Jan Wieck
Subject Re: Open 7.4 items
Date
Msg-id 3F817026.8000204@Yahoo.com
Whole thread Raw
In response to Re: Open 7.4 items  (Tom Lane <tgl@sss.pgh.pa.us>)
List pgsql-hackers
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 #



pgsql-hackers by date:

Previous
From: Tom Lane
Date:
Subject: Re: Open 7.4 items
Next
From: Andrew Dunstan
Date:
Subject: Re: more on initdb