Postgres index timestamp with timezone column

Question

I'm running PostgreSQL 9.6, and I have a table named decks with an expiration column of type timestamp with time zone (for storing decks of cards where each card can expire independently).

I'd like to create a nightly cron job that finds all cards which expired at any point during the previous day—i.e. between 0:00 and 23:59 inclusive.

This seems to gives me the time range I want...

SELECT id
  FROM decks
 WHERE expiration >= (now()::date - 1)::timestamptz
   AND expiration < (now()::date)::timestamptz;

...but I'm wondering two things:

1. What's the best way to index the expiration column for my scenario?
2. Is there a better/cleaner way to specify the start and end times?

Answer 1

Question 1: For that query, a standard index is the best option. However, see below.

Question 2: Lots of options, here. A quick change to your query:

SELECT id
  FROM decks
WHERE expiration::date = (now()::date - 1);

... allows you to create a functional index on expiration::date which should be smaller, and a bit more efficient.

Personally, I'd go a bit further and use current_date instead of now():

SELECT id
  FROM decks
WHERE expiration::date = (current_date - 1);

As always, I recommend use of EXPLAIN and EXPLAIN ANALYZE when evaluating indexes.