Thread: Primary Key

Primary Key

From
Joey Quinn
Date:
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

Re: Primary Key

From
Elliot
Date:
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.



Re: Primary Key

From
Joey Quinn
Date:
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.


Re: Primary Key

From
Joey Quinn
Date:
yep, that worked... thanks again.


On Thu, Nov 21, 2013 at 4:38 PM, Joey Quinn <bjquinniii@gmail.com> wrote:
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.



Re: Primary Key

From
"Joshua D. Drake"
Date:
On 11/21/2013 12:40 PM, 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?

It will finish but it is checking that not only is it unique, but NOT
NULL. You may want to cancel it and try:

select count(*) from foo where bar IS NULL;

If you have NULL in the column, you don't have a primary key

Joshua D. Drake

>
> Joey
>


--
Command Prompt, Inc. - http://www.commandprompt.com/  509-416-6579
PostgreSQL Support, Training, Professional Services and Development
High Availability, Oracle Conversion, Postgres-XC, @cmdpromptinc
For my dreams of your image that blossoms
    a rose in the deeps of my heart. - W.B. Yeats


Re: Primary Key

From
Joey Quinn
Date:
From the way the table was filled, I knew there were no nulls. It succeeded.



On Thu, Nov 21, 2013 at 8:15 PM, Joshua D. Drake <jd@commandprompt.com> wrote:

On 11/21/2013 12:40 PM, 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?

It will finish but it is checking that not only is it unique, but NOT NULL. You may want to cancel it and try:

select count(*) from foo where bar IS NULL;

If you have NULL in the column, you don't have a primary key

Joshua D. Drake


Joey



--
Command Prompt, Inc. - http://www.commandprompt.com/  509-416-6579
PostgreSQL Support, Training, Professional Services and Development
High Availability, Oracle Conversion, Postgres-XC, @cmdpromptinc
For my dreams of your image that blossoms
   a rose in the deeps of my heart. - W.B. Yeats