Re: Primary Key - Mailing list pgsql-general

From Joey Quinn
Subject Re: Primary Key
Date
Msg-id CAG5XHYnp3pVXKjzDp3i-AeTT7vyo4hGNreF_=j3t4Ky3K_4w0A@mail.gmail.com
Whole thread Raw
In response to Re: Primary Key  (Elliot <yields.falsehood@gmail.com>)
Responses Re: Primary Key
List pgsql-general
Ahhh, that's what I was missing... thank-you. (just launched, we'll see how that one goes).



On Thu, Nov 21, 2013 at 3:48 PM, Elliot <yields.falsehood@gmail.com> wrote:
On 2013-11-21 15:40, Joey Quinn wrote:
I have a table (5 columns) with approximately 670 million rows. It has had an index (unique) on an inet column from the beginning. Today I added a primary key constraint based on the same column thinking that since it already had an index, this would be a relatively quick operation. That does not appear to be case. It has gone into a "not responding" status for an hour or so now. As a point of reference, I'm using 9.3 on a 64 bit Windows Server 2008 (32 GB ram) and inserts so far have taken 6 1/2 - 7 minutes for each batch of 16.7 million rows.

Other than not creating the primary key at the beginning, did I do anything wrong? and can I reasonably expect the current operation to finish?

Joey

I'm guessing you're creating the primary key without designating your current unique index as the index to use for the constraint.
Mark your column as not null if it isn't already then do an "alter table table-name add primary key using index whatever-the-name-of-your-extant-unique-index-is".
Otherwise you're building another separate index for that constraint, which you don't need to do.


pgsql-general by date:

Previous
From: Ken Tanzer
Date:
Subject: Getting non_NULL right-side values on a non-matching join?
Next
From: Kevin Grittner
Date:
Subject: Re: corruption issue after server crash - ERROR: unexpected chunk number 0