Today I Learned

A Hashrocket project

Ever so slightly faster intervals in postgres

When working with dates in postgres I often construct an interval like so:

> select (2 || ' days')::interval;
2 days

Which is fine! But there’s an alternative that’s ever so slightly faster.

> select 2 * '1 day'::interval;
2 days

The time difference can be seen over large quantities of data, like 10 million.

> select (x || ' days')::interval from generate_series(1, 10000000) as x;
...
Time: 10437.314 ms
 > select x * '1 days'::interval from generate_series(1, 10000000) as x;
...
Time: 7831.568 ms

Microbenchmarking in action! Remember, take all microbenchmarks with a grain of salt.

Looking for help? Hashrocket developers believe that data quality is as important as code quality. We enjoy all the challenges of relational databases, from finding the fastest index, to structuring data to fit the needs of an application. We're eager to share our experiences; check out PG Casts, a series of free weekly PostgreSQL screencasts.