Are PRIMARY KEYs just UNIQUE INDEXes?
От | Jules Bean |
---|---|
Тема | Are PRIMARY KEYs just UNIQUE INDEXes? |
Дата | |
Msg-id | 20000823113444.C17510@grommit.office.vi.net обсуждение исходный текст |
Ответы |
Re: Are PRIMARY KEYs just UNIQUE INDEXes?
|
Список | 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
В списке pgsql-general по дате отправления: