How Long Does It Take To Create An Index?

A recent tweet asked the following question.

Does anybody know how long it should take to create an index of two integer columns with approximately 110 Million records in the DB? #postgres #postgresql #NotAnDBA

Let's put together an experimental table, populate 110,000,000 million records with random values, and collecting a iming of the CREATE INDEX for a single column index.

-- create table with two integer columnsCREATE TABLE t (    a smallint,    b smallint);

-- Insert 110,000,000 million records, with random values for columns a and bINSERT INTO t(a,b)SELECT  (RANDOM() * 1000)::INT,  (RANDOM() * 1000)::INTFROM GENERATE_SERIES(1, 110000000) seq;

SELECT COUNT(*) FROM t;   count----------- 110000000

Turn timing on, and run the CREATE INDEX.

-- turn timing on, display time\timingTiming is on.-- Create a single column index, on the "a" column of table "t"CREATE INDEX t_a_idx ON t (a);Time: 52348.022 ms (00:52.348)

Answer

So a quick test produced a result of 52 seconds.

Experiment Details

• Tested on a M1 MacBook Air, with 16GB RAM, and no other significant system load.
• The table has no constraints like primary keys or foreign keys.
• Vacuum had been running, and was cancelled.
• There are no other queries on the table.

This post is intended as a demonstration of how to conduct a testing process to answer this type of question. The best way to answer this kind of question is to test it on the same server, under realistic conditions while other queries are occurring. When other queries are happening on the table, remember to create the index using the CONCURRENTLY keyword.