Best sort order in B-tree index to support query on recent rows?

Question

Suppose that I have a table which have a description like:

create table my_table (
  id serial, 
  create_date timestamp with time zone default now(),
  data text
);

and a query like:

select * from my_table
where create_date >= timestamp with time zone 'yesterday'

Which index will be theoretically faster and why?

create index index_a on my_table (create_date);

create index index_b on my_table (create_date DESC);

Answer 1

_{I dislike the name "create_date" for a column that's not actually a date but a timestamptz. Using "created_at" instead.}

Since created_at can be NULL, this 3rd variant will be faster (even if not by much):

CREATE INDEX index_c ON my_table (created_at DESC NULLS LAST);

NULL values sort after the greatest value by default. DESCENDING sort order is the perfect inversion, so NULL values go first. See:

• Sort by column ASC, but NULL values first?

Postgres can scan B-tree indexes backwards at almost the same speed, so both your variants are almost on par. But the operator >= excludes NULL values (like most operators). So Postgres has to skip leading / trailing NULL values respectively first. Typically not expensive, but still.

The index with DESC NULLS LAST (or NULLS FIRST) has the greatest values first and NULL values last (or vice versa), so the query can start reading right from the top (bottom) of the index directly.

If there cannot be NULL values, there will be no noticeable difference. And you should declare the column NOT NULL. (And you should have said so.)

If inserts come with strictly ascending timestamps (and there are no updates!) - or if that's at least true for recently inserted rows since "yesterday", (relevant) rows are physically clustered by timestamp automatically. Else, it can pay to physically cluster rows from time to time. (While not interfering with concurrent load on the database!) That can make a bigger difference, as it keeps the number of data pages that have to be read to a minimum. See:

• Configuring PostgreSQL for read performance

If your table is big, a partial index can pay:

CREATE INDEX index_c_partial ON my_table (created_at DESC NULLS LAST)
WHERE  created_at >= '2021-06-26 0:0';  -- recent but before yesterday

It cuts off the majority of old rows, so that the index shrinks to a fraction in size.

But since your cut-off ('yesterday') is a moving target you'll have to recreate that index from time to time to remove old tuples, or the benefit will deteriorate over time. Like, daily, weekly, monthly - you decide.

With warm cache, that partial index will not be much faster than the full index, but since it's so much smaller, its chances to stay in cache are bigger accordingly (depends on your complete setup), which typically makes a big difference. (And it does not occupy as many resources to begin with.)

Since we have such a small index now, and while we only deal with so few columns (or you do not actually need SELECT * to begin with?!), we might as well make it a covering index (Postgres 11 or later):

CREATE INDEX index_c_partial_covering ON my_table
   (created_at DESC NULLS LAST) INCLUDE (id, data)
WHERE  created_at >= '2021-06-26 0:0';

Again, details depend on the complete situation. Related:

• Do covering indexes in PostgreSQL help JOIN columns?

If some preconditions are met you get cheaper index-only scans now. The physical order of rows in the table does not matter on this case.

---

Oh, and move that timestamptz column to a different position in the table definition. The way you have it now maximizes bloat due to alignment padding. Any other position for the timestamptz column is better. Like:

CREATE TABLE my_table (
, created_at timestamptz DEFAULT now() NOT NULL
, id serial NOT NULL PRIMARY KEY
, data text
);

See:

• Calculating and saving space in PostgreSQL