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.

This error is not caused by statement_timeout, but by a user cancel request. What interface do you use to access PostgreSQL? Try setting the s_t value to 0, which turns this off. — MatheusOl
– MatheusOl, Commented Sep 17, 2013 at 1:45
I'm using psql to access the server remotely (this is Heroku's Postgres offering, so I don't have direct server access). — Jacob Gillespie
– Jacob Gillespie, Commented Sep 17, 2013 at 14:04
I'll try creating the index with statement_timeout set at 0 — Jacob Gillespie
– Jacob Gillespie, Commented Sep 17, 2013 at 14:06
I tried with statment_timeout set to 0, and it still failed... I'm not really sure why... — Jacob Gillespie
– Jacob Gillespie, Commented Sep 17, 2013 at 21:27
Looks like Heroku is killing your connection, check with their support if the really do that. Also, try increasing maintenance_work_mem to the higher value you can, this will improve the index creating. — MatheusOl
– MatheusOl, Commented Sep 24, 2013 at 18:13

Rots · Accepted Answer · 2013-09-17 01:46:48Z

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).

answered Sep 17, 2013 at 1:46

Rots

5,5964 gold badges47 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Jacob Gillespie Over a year ago

It definitely does have non-unique numbers - I want to create a simple index vs a unique index. I don't think it requires unique values with this syntax (it worked on smaller tables).

Rots Over a year ago

@JacobWG Thanks for clearing that up. Perhaps try the partial index?

Jacob Gillespie Over a year ago

after I create the partial index, then what? Do I create multiple partial indexes? Can I combine them into one?

Rots Over a year ago

Because your data contains non-unique numbers, it usually indicates a common value (perhaps the default of 0?) which will not need indexing if 90% of the values are 0. Therefore your partial index could cover values greater than 0. More info here: postgresql.org/docs/8.1/static/indexes-partial.html

Jacob Gillespie Over a year ago

Yeah, they all need indexing... :/ This is a join table between two other tables, so each field refers to a primary key of another table.

Andrew Lazarus · Accepted Answer · 2013-09-18 16:59:32Z

1

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.

answered Sep 18, 2013 at 16:59

Andrew Lazarus

19.5k3 gold badges38 silver badges57 bronze badges

Comments

Jacob Gillespie · Accepted Answer · 2013-09-25 18:13:38Z

1

It was Heroku killing connections (the server ran out of temporary space). Contacting Heroku support was the solution eventually...

answered Sep 25, 2013 at 18:13

Jacob Gillespie

4,0813 gold badges26 silver badges34 bronze badges

Collectives™ on Stack Overflow

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

3 Answers 3

5 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related