PostgreSQL: How to create index on very large table without timeouts?

Question

I am trying to add a simple index with the following SQL in Postgres, but the command keeps timing out:

CREATE INDEX playlist_tracklinks_playlist_enid ON playlist_tracklinks (playlist_enid);

The table definition is as follows:

=> \d playlist_tracklinks
         Table "public.playlist_tracklinks"
     Column     |     Type      |     Modifiers
----------------+---------------+--------------------
 playlist_enid  | numeric(20,0) | not null default 0
 tracklink_enid | numeric(20,0) | not null default 0
 position       | integer       | not null default 1

There are around 2.2 billion rows in the table, and it fails with the following error:

ERROR:  canceling statement due to user request

I tried increasing the query timeout time with the following:

SET statement_timeout TO 360000000;

However it still hits that threshold. I have tried with and without CONCURRENTLY, and am sort of at a loss for what to do. Any suggestions would be greatly appreciated.

Answer 1

You could try indexing a part-piece of the table, say the first 10k rows using the WHERE statement. Then you might be able to see if that works and how long it takes. Reference for using WHERE with CREATE INDEX here: http://www.postgresql.org/docs/9.1/static/sql-createindex.html

Is it possible your column contains non-unique numbers? That could potentially cause an issue (I'm not sure if an index requires unique values on a column in this case).

Answer 2

Arithmetic with numerics is very slow. This includes the comparisons needed to build and use the indexes. I suggest that you change the enid types to char(20) or just varchar if you do not do any arithmetic (other than comparisons) on them, and perhaps bigint if you do. Bigint isn't quite enough for the largest possible 20-digit number—I don't know what sort of information this ids carry around, if they can really be that big.