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

All we need is an easy explanation of the problem, so here it is.

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);

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 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:

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:

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:

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:

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply