Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
I am currently importing Wikipedia pagelinks in my postgres database, and it takes forever (three days and still running). Is it possible to drop the primary key index while the inserts are still running, and would this speed things up?
Actually, if this changes anything, it is a uniqueness constraint over three columns that creates the index.
Yes, a plain INSERT is fastest when no indexes have to be maintained. So it may pay to drop indexes before huge bulk inserts and add them later. Depends on the whole situation, of course.
No, you cannot drop a PK or a UNIQUE constraint or even a plain index in the middle of an INSERT, which also writes to the index.
No, I don't think your query should take more than three days, even if you import every link that every Wikipedia site on earth ever had to offer. There is probably something less than ideal in your command or setup or hardware or how you read / write the data.
For starters, an operation of that magnitude would rather use COPY:
DROP (or CREATE) the index between statements inside a single transaction. Concerning single inserts: dba.stackexchange.com/questions/63055/…
Feb 20, 2015 at 17:25