I’ve recently started fiddling with PostgreSQL performance optimization. Here is a little benchmark that I’ve compiled in order to investigate impact of Postgres indexes usage on query execution times and disk usage.
Benchmark setup
The benchmarks included in this post were conducted using PostgreSQL running under Ubuntu 18.04 on a desktop PC with the following specs:
- CPU: Intel Core i7-6700k
- RAM: 32GB DDR4 @ 3000MHz
- SSD: Crucial MX200 (SATA3)
The CPU’s stock clock is at 4.00GHz. Turbo Boost was disabled in the BIOS, as otherwise the CPU clocks could raise after approximately 100ms under single-threaded load causing inconsistencies in workload duration measurements (especially when comparing longer running queries with shorter ones). PostgreSQL RDBMS was installed locally with no additional virtualization layers. PostgreSQL data directory was located on the same partition of the SSD drive, on which the operating system was installed.
In order to conduct the benchmarks, multiple tables with the same schema were created, each having the following columns:
- fixed:
INT, seeded with random values from 1 to 1000 regardless of the number of records - scaled:
INT, seeded with random values from 1 to n (number of records in the table) - shortstring:
VARCHAR(8), seeded with an 8-character substring of MD5 hash of a random number - longstring:
VARCHAR(64), seeded with a 64-character string consisting of two concatenated, random MD5 hashes - dt:
DATE, seeded with a random date between 1 year fromNOW()and 11 years fromNOW()
The tables were then seeded with a given number of rows (n: 2k, 8k, 32k, 128k, 512k) using:
create table bench_512k (fixed int, scaled int, shortstring varchar(8), longstring varchar(64), dt date);
insert into bench_512k
select floor(random()*1000+1)::int,
floor(random()*512000+1)::int,
substring(md5(random()::text), 1, 8),
concat(md5(random()::text), md5(random()::text)),
NOW() + (random() * (NOW()+'10 years' - NOW())) + '1 years'
from generate_series(1, 512000);Each column in every table had a B*-tree index created on it. On top of that, and additional index was created on column expression: SUBSTRING(LONGSTRING, 3, 2).
Test queries were timed using EXPLAIN ANALYZE where possible. Time to create an index was measured using \timing. The following query planning settings: enable_bitmapscan, enable_indexscan, enable_indexonlyscan and enable_seqscan were changed as necessary during testing in order to ensure the usage of index scans instead of sequential scans (or the other way around).
Query execution time improvements with indexes vs sequential scans
The first set of test queries was performing selections on fixed column – the one containing random numbers between 1 and 1000 regardless of the number of records in the table. An example test query (selecting 0.4% of the table records) is listed below:
EXPLAIN ANALYZE SELECT * FROM BENCH_2K WHERE FIXED > 996;For baseline, execution time of exactly the same query with index scans disabled was measured.
The usage of indexes brings the biggest benefits when performing a query that selects only a small fraction of the table records. When selecting as much as 50% of the records stored in the table via bitmap heap scan, performance was roughly the same as when performing a sequential scan. With the query selectivity at 99%, the query execution time was on average 20% slower as compared to sequential scan.
Another test consisted of a set of queries with selectivity of 12.5% on a table with n=32k records. Conditions in where clause were performing a comparison on each column separately (in separate queries). Similarly to the first chart, improvement over sequential scan was calculated.
Query Optimizer cost estimates accuracy
The accuracy of Query Optimizer cost estimates was initially measured by performing queries with selectivity of 12.5% on a table with n=32k records with comparison in the where clause performed on each indexed column/column expression separately. Since the Query Optimizer estimates are returned in arbitrary computational units, they were divided by query execution times (resulting in acu/ms).
Ideally accurate Query Optimizer would return estimates that when divided by actual query execution time would result in a constant value across analyzed queries. Given the fact that an SSD drive was used, I’ve decided to change random_page_cost from it’s default value (4) to 1 and examine it’s impact on query optimizer estimation accuracy. After having altered random_page_cost value, the standard deviation of optimizer units per millisecond of query execution in the test queries (n=32k, selectivity=12.5%) dropped from 15.7 down to 9.2 with the average dropping by only 9%. The results are illustrated in the chart above.
Additionally, another graph illustrating the same arbitrary computational unit per millisecond of execution ratio was prepared:
This graph is based on the same test runs as the very first graph in this post. It’s data comes from queries with comparison on column “fixed” in their where clause, with different selectivity and different number of records in the table. During these runs, random_page_cost was set to the default value (4). When selectivity is low, the Query Optimizer expects that only a small subset of blocks needs to be accessed and expects these to be read via random read. Due to the fact that the database was running off an SSD (which can perform random reads roughly as fast as sequential ones), RDBMS managed to finish the work faster than anticipated. As a result, values plotted on the chart are raising as selectivity decreases (within a constant number of records).
Performance degradation of INSERT commands when using indexes
In order to measure INSERT performance, all the data stored in previously seeded benchmark tables was copied over to corresponding non-indexed tables. On both groups of tables an INSERT command was performed that generated (similarly to how database seeding was done in the first place) n/2 new records. The graph below illustrates comparison between inserts performed on indexed and non-indexed tables:
INSERT performance decreases significantly on a table with indexes, especially as the number of it’s records increases. In the case table with 2k records, inserting 1k new records took twice as long as in the index-free table. The one with 512k records to start with took over 7 times longer than it’s non-indexed counterpart when inserting 256k newly generated records.
Postgres index disk space usage
After the indexes on each test table were created, their disk usage was determined using pg_relation_size and then compared to the table disk size.
It shall be noted that since indexes are stored in blocks (size of each is 8192B), all of the results of pg_relation_size are multiples of that value. For example in the case of the table with 2k records, indexes on: fixed, scaled, date and substring(longstring) all take 65536B each, which is eaxctly 8 blocks.
As expected, disk usage of an index is highly impacted by the size of the column (the bigger the size, the less of them can fit in a single index block). It’s worth noting that said relationship is not entirely linear. For example: fixed, scaled and date columns all are 4 bytes and yet their indexes take the same space as substring(longstring) which is just 2 bytes. Similarly, even though longstring column is 8 times as big as shortstring, it’s index only takes roughly 3 times as much space.
On top of that, the index disk space usage is also impacted by the number of records in the table (the more values to be stored in the index, the more space it will take).
Additionally, index disk space usage in relation to table size seems to decrease slightly as the number of records increases. It might be due to the fact that when the number of blocks used by the index is low (for example: 8, as described above), disk space within the leaf node block not being utilized fully makes more of an impact on the total disk space utilization as compared to when there are more blocks in use.
New index creation times
The following chart shows index creation times of each index created on tables with different numbers of records:
As expected, index creation time is predominantly determined by the number of records. Since each insert operation performed on a B-tree has a complexity (both average and worst case) of O(logn) and there are n records to be added, the complexity of building an entire B-tree is O(nlogn).
Apart from the number of records, index creation time is also affected by the column size. Performing a comparison of two strings is more time consuming than comparing two integers (especially if the strings are particularly long). On top of that, bigger column values will fill up the index blocks faster (will require more of them), which will result in more time spent on disk write operations while building an index.