Are PRIMARY KEYs just UNIQUE INDEXes?

From: Jules Bean <jules(at)jellybean(dot)co(dot)uk>
To: pgsql-general(at)postgresql(dot)org
Subject: Are PRIMARY KEYs just UNIQUE INDEXes?
Date: 2000-08-23 10:34:45
Message-ID: 20000823113444.C17510@grommit.office.vi.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi all,

I have a few questions about how postgres uses primary keys.

I initially created a table with a SERIAL primary key. In fact (in a
piece of appaling deviation for relational theory :-( but the tables
are so huge) the table has a perfectly natural primary key (a
two-column one, as it happens). I built a unique index on that
one. And I also built a non-unique index on a different field.

Then I had a huge amount of data to bulk import, so I followed
standard advice and dropped the indexes, including the 'implied' index
tablename_pkey.

Import went fine (and very fast!).

Then I rebuilt the indexes. I didn't bother to rebuild the _pkey
index, since I'm not actually using it at the moment (its destiny is
to serve as the target for a foreign key, but that isn't operational).

Now after a few days, I'm noticing inserts going /much/ slower.
Whereas I was seeing insert speeds of about 40,000 per minute, I'm now
seeing over an hour to insert 10,000. Unfortunately, I've been making
too many changes to be sure what caused this (and I'm going to run
vacuum analyze again in a bit), but I'm wondering if it's confused
about the primary key. So, my question is:

Does postgres remember that something is a primary key? Or is PRIMARY
KEY really just a shorthand for creating a unique index?

Secondly, what is typically the slowest part of an insert? Presumably
it checks all the unique indices, but that should be quick, right?

Jules

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Martijn van Oosterhout 2000-08-23 11:22:12 Re: DateStyle causes drama during upgrade
Previous Message Andrew.Mason 2000-08-23 10:08:27 Returning an AutoInc ID on Insert