We use the CREATE INDEX statement to create an index on the specified columns of the specified table.
Indexes are primarily used to improve query performance.
YugabyteDB automatically backfills the existing data into the index via an online schema migration.
Example:
yugabyte=# CREATE TABLE demo (id INT PRIMARY KEY, c1 INT, c2 INT, c3 INT);
CREATE TABLE
yugabyte=# INSERT INTO demo VALUES (1, 1, 1), (2, 2, 2), (3, 3, 3);
INSERT 0 3
yugabyte=# \timing on
Timing is on.
yugabyte=# CREATE INDEX demo_idx1 ON demo(c1);
CREATE INDEX
Time: 4024.253 ms (00:04.024)
As you can see, online index creation tends to be a little slow. In the example, it took 4 seconds to create the index.
If you are positive that there will be no concurrent DML happeing while an index is being created, you can create it noncourrently – which is ALOT faster!
yugabyte=# CREATE INDEX NONCONCURRENTLY demo_idx2 ON demo(c2);
CREATE INDEX
Time: 606.412 ms
Have Fun!
