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);
  • 1
    Run explain (analyze, buffers) and you'll see if there is a difference. Jun 27, 2021 at 17:48

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

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
, data text


  • "the index shrinks to a fraction in size" -- But the depth of the BTree does not change much, hence there is little performance benefit.
    – Rick James
    Aug 26, 2021 at 19:45
  • @RickJames: While that's true in theory, you can still gain a lot when resources are limited - which is the typical case. Reading past the quote, you'll find I addressed that point exactly. Aug 26, 2021 at 21:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.