Re: Are PRIMARY KEYs just UNIQUE INDEXes?

Jules Bean <jules(at)jellybean(dot)co(dot)uk> writes:
> Does postgres remember that something is a primary key? Or is PRIMARY
> KEY really just a shorthand for creating a unique index?

pgsql does remember that an index was created via a PRIMARY KEY
declaration, but AFAIR the only thing that flag does is indicate
the logical "primary key" in situations like interpreting a REFERENCES
constraint that doesn't mention the specific column to use. It
certainly has no bearing on the speed of inserts.

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

If you don't have any constraints or other fancy features, I'd think
the insertion of the new index entries is probably the slowest thing.
Uniqueness checks are done as part of that; they don't add much overhead
*unless* you are constantly deleting and reinserting rows with the same
key value (note that UPDATE is equivalent to delete+reinsert, too).
In that case, there are still index entries for the deleted rows,
and the system has to actually visit all the deleted rows with the same
key as the to-be-inserted row to verify that they're all deleted.
VACUUM will clean this up by physically removing the deleted rows and
their index entries.

Dunno if that explains your problem or not...

regards, tom lane