Without indexes, general purpose relational databases lose efficient search, unique constraints and primary keys. On realistic data volumes, good performance is unattainable without them. The cost of building those structures on very large tables — sorting followed by insertion into the index — is what the measurements below examine.
Test data
The demo set is a single table with a billion rows, created as follows:
|
1 2 3 4 5 6 7 |
blog=# CREATE TABLE t_data AS SELECT id::int4, (random() * 1000000000)::int4 AS i, (random() * 1000000000) AS r, (random() * 1000000000)::numeric AS n FROM generate_series(1, 1000000000) AS id; Time: 1569002.499 ms (26:09.002) |
Its properties matter later. The id column is an ascending number, which is significant during index creation. The second column holds a random value multiplied by the number of rows, cast to integer. The third column is double precision, and the last stores comparable data as numeric — a floating point type that does not use the CPU's floating point unit internally.
Table size can be checked once the rows exist:
|
1 2 3 4 5 |
blog=# SELECT pg_size_pretty(pg_relation_size('t_data')); pg_size_pretty ---------------- 56 GB (1 row) |
Before comparing runs, set the hint bits so the measurements stay fair:
|
1 2 3 |
blog=# VACUUM ANALYZE; VACUUM Time: 91293.971 ms (01:31.294) |
Progress of that operation can be followed through a system view, which is also useful for diagnosing long VACUUM runs:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
demo=# SELECT * FROM pg_stat_progress_vacuum; -[ RECORD 1 ]------+-------------- pid | 5945 datid | 1515520 datname | blog relid | 1515537 phase | scanning heap heap_blks_total | 7352960 heap_blks_scanned | 212599 heap_blks_vacuumed | 0 index_vacuum_count | 0 max_dead_tuples | 291 num_dead_tuples | 0 |
Individual indexes on the columns are then created simply:
|
1 2 3 4 5 6 7 8 |
test=# \d t_data Table "public.t_data" Column | Type | Collation | Nullable | Default --------+------------------+-----------+----------+--------- id | integer | | | i | integer | | | r | double precision | | | n | numeric | | | |
Index creation on the default configuration
All timings below use PostgreSQL defaults and were taken on an AMD Ryzen Threadripper 2950X 16-Core processor. Each column is indexed in turn:
|
1 2 3 |
blog=# CREATE INDEX ON t_data (id); CREATE INDEX Time: 291700.318 ms (04:51.700) |
Around five minutes per index, before any tuning — with a billion rows. The system view reveals what the backend is doing:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
blog=# SELECT * FROM pg_stat_progress_create_index; -[ RECORD 1 ]------+------------------------------- pid | 5945 datid | 1515520 datname | blog relid | 1515537 index_relid | 0 command | CREATE INDEX phase | building index: scanning table lockers_total | 0 lockers_done | 0 current_locker_pid | 0 blocks_total | 7352960 blocks_done | 703280 tuples_total | 0 tuples_done | 0 partitions_total | 0 partitions_done | 0 |
First the table is scanned and data is prepared for the sort that follows:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
-[ RECORD 1 ]------+------------------------------------ pid | 5945 datid | 1515520 datname | blog relid | 1515537 index_relid | 0 command | CREATE INDEX phase | building index: sorting live tuples lockers_total | 0 lockers_done | 0 current_locker_pid | 0 blocks_total | 7352960 blocks_done | 7352960 tuples_total | 0 tuples_done | 0 partitions_total | 0 partitions_done | 0 |
The sort is the expensive part and deserves proper tuning. Once sorting is finished, the sorted tuples are inserted into the index:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
-[ RECORD 1 ]------+--------------------------------------- pid | 5945 datid | 1515520 datname | blog relid | 1515537 index_relid | 0 command | CREATE INDEX phase | building index: loading tuples in tree lockers_total | 0 lockers_done | 0 current_locker_pid | 0 blocks_total | 0 blocks_done | 0 tuples_total | 1000000000 tuples_done | 263748386 partitions_total | 0 partitions_done | 0 |
Indexing the second, randomly distributed column takes noticeably longer despite identical data type and volume, roughly two extra minutes — the sort is substantially larger when rows arrive in random order:
|
1 2 3 |
blog=# CREATE INDEX ON t_data (i); CREATE INDEX Time: 401634.592 ms (06:41.635) |
Data type matters as much as physical order:
|
1 2 3 |
blog=# CREATE INDEX ON t_data (r); CREATE INDEX Time: 441994.372 ms (07:21.994) |
The double precision column is another 40 seconds slower; a double precision value is larger than an integer, which contributes to the difference.
The numeric column behaves differently again:
|
1 2 3 |
blog=# CREATE INDEX ON t_data (n); CREATE INDEX Time: 799659.658 ms (13:19.660) |
Two factors dominate: whether the data is already sorted, and which data type is indexed. Both are routinely underestimated by people who reach only for more RAM and more CPUs.
Over-tuning
Throwing hardware and parameters at the problem is the usual first response, typically through these settings:
|
1 2 3 4 5 6 7 8 |
blog=# ALTER SYSTEM SET max_wal_size TO '100 GB'; ALTER SYSTEM blog=# ALTER SYSTEM SET max_parallel_maintenance_workers TO 10; ALTER SYSTEM blog=# ALTER SYSTEM SET maintenance_work_mem TO '16 GB'; ALTER SYSTEM blog=# ALTER SYSTEM SET shared_buffers TO '64 GB'; ALTER SYSTEM |
A restart is required afterwards; without changing shared_buffers, SELECT pg_reload_conf() would suffice. In detail:
- max_wal_size — controls the distance between checkpoints and the size of the WAL, reducing I/O volume and improving I/O speed generally.
- max_parallel_maintenance_workers — the upper limit on worker processes PostgreSQL may start for index builds.
- maintenance_work_mem — how much memory each operation may use.
- shared_buffers — the size of the I/O cache.
Re-running the index creation:
|
1 2 3 |
blog=# CREATE INDEX ON t_data (n); CREATE INDEX Time: 474907.805 ms (07:54.908) |
Time dropped from about 13 min 19 sec to under 8 minutes — not even a doubling of speed. The CPU view explains why:
|
1 2 3 4 5 6 7 8 9 |
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 8773 hs 20 0 67.8g 2.2g 196256 R 99.0 1.8 1:05.88 postgres 8824 hs 20 0 66.6g 968576 170576 R 99.0 0.7 0:18.96 postgres 8825 hs 20 0 66.6g 1.0g 170796 R 99.0 0.8 0:18.98 postgres 8826 hs 20 0 67.4g 1.8g 170796 R 99.0 1.4 0:18.97 postgres 8827 hs 20 0 67.4g 1.8g 170796 R 99.0 1.4 0:18.95 postgres 8829 hs 20 0 66.6g 990.8m 170796 R 99.0 0.8 0:18.93 postgres 8823 hs 20 0 67.4g 1.8g 170796 R 98.7 1.4 0:18.95 postgres 8828 hs 20 0 66.7g 1.0g 170376 R 98.7 0.8 0:18.95 postgres |
The sample was caught during the sort. Only the sort phase parallelizes well; many other stages cannot run in RAM or concurrently, so even with many cores the gain stays modest. The limiting factor is the local SSD at roughly 600 MB/sec during sorting. More striking still: the default configuration sorting integer values is faster than a fully parallel index creation with the raised parameter settings.
Configuration tuning is therefore not the only lever — choosing the right data type can make just as large a difference.
