CREATE INDEX: Data types matter

In the PostgreSQL world, as well as in order database systems, data types play a crucial role in ensuring optimal performance, efficiency, as well as semantics. Moreover, some data types are inherently easier and faster to index than others. Many people are not aware of the fact that indexes indeed make a difference, so let us take a look and see how long it takes to index the very same data using different
types.

Creating some sample data

To show which differences a data type makes, we first have to create some sample table. In this case, it contains 5 data types that we want to inspect to understand how they behave:

Finally, we use our good old friend (the generate_series function), which has served me very well over the years:

What this does is generate 50 million random rows and put them into my table. Note that all column entries are identical to ensure fairness for our index creation.

The data might look as follows:

Note that PostgreSQL does not necessarily write data to disk immediately. In addition to that, there is a thing known as "hint bits", which can impact performance. We want to ensure that we can compare the time needed to create those indexes, so we VACUUM the table (for hint bits) and force a checkpoint to force everything to disk:

Running CREATE INDEX for real

In this example, all tests are using the default settings of PostgreSQL, which are of course suboptimal and can be tuned as described in one of our more successful blog posts about CREATE INDEX.

One of the key settings (to be found in postgresql.conf) controlling indexing speed is maintenance_work_mem. For this quick benchmark, we kept the standard setting of 64 MB:

Let us create the index on the integer column first:

As we can see, this is the fastest process we can expect - indexing integer is really quick because CPUs are made to handle numbers. For 64 bit integers, we see similarly good results:

However, the story starts to change if we move from integer numbers to floating point values. The process starts to be slower:

While native CPU floating point numbers (we are using ARM chips here) are still reasonably okay, we will see a massive slowdown compared to integer values when we move to "numeric":

What is the purpose of "numeric" anyway? The answer is: Accounting. Floating point numbers have different precision depending on the magnitude of the number. "numeric" ensures that the precision is exactly the way we want it to be, regardless of small or large values. Therefore, we should always use "numeric" for money, accounting, and so on. One thing has to be kept in mind at all times:
The result has to always be correct - even if it takes longer to calculate it. Therefore, one should never use "float" to handle currency.

Obviously, the by far most expensive data type is "text". Here are the numbers:

Obviously, one would not want to store numbers as text anyway, but I decided to put the value here for comparison.

Comparing results

After running those tests, we can compare the results and see what happens. The following graph visualizes the results:

What is interesting here is that the difference in speed is quite substantial. Most people greatly underestimate those changes in performance and thus it makes sense to spread some awareness among readers.