Including a column twice in a PostgreSQL index

I’m building a PostgreSQL based work-queue as described here.

My queue definition differs slightly from the concept described in the article by using a target timestamp for consumption (due_time):

CREATE TABLE publish_queue
(
  id INT generated BY DEFAULT AS IDENTITY PRIMARY KEY,
  article_id INT NOT NULL,
  due_time TIMESTAMP NULL,
  is_complete BOOLEAN NOT NULL DEFAULT false,
  completed TIMESTAMP NULL,
  created TIMESTAMP NOT NULL DEFAULT NOW()
);

There’s a single index:

CREATE INDEX idx_publish_due_created ON publish_queue(
due_time ASC NULLS FIRST, created ASC) WHERE(NOT is_complete);

Let’s fill the table with a test dataset:

begin transaction;
INSERT INTO publish_queue(article_id, due_time)
SELECT 2407, t.day::date FROM generate_series(timestamp '2024-03-07', timestamp '2034-08-16',interval  '1 minute') AS t(day);
commit;

And consume an item:

update publish_queue
set is_complete = true, completed = now()
where id = (
  select id
  from publish_queue
  WHERE NOT is_complete AND (LOCALTIMESTAMP >= due_time OR due_time IS NULL)
  order by due_time ASC NULLS FIRST, created ASC
  for update skip locked
  limit 1
)
returning article_id;

On my system the query takes ~2 seconds to complete with the following execution plan:

https://explain.depesz.com/s/oLYI

BUT, if I change the index like below, including the due_time a second time – something I have never done before …

CREATE INDEX idx_publish_due_created ON publish_queue(
due_time, due_time ASC NULLS FIRST, created ASC) WHERE(NOT is_complete);

the query takes 60ms and results in this plan:

https://explain.depesz.com/s/U3LA

Now my question is, it feels weird to include a column two times in a index. Am I missing something here? Could the same be achieved using a different index or rephrasing the query?

Answer

Yes, it is weird. You can do better:

CREATE INDEX ON (coalesce(due_time, '-infinity'::timestamp), created);

Then query like this:

SELECT id
FROM publish_queue
WHERE NOT is_complete
  AND LOCALTIMESTAMP >= coalesce(due_time, '-infinity'::timestamp)
ORDER BY coalesce(due_time, '-infinity'::timestamp), created
FOR UPDATE SKIP LOCKED
LIMIT 1

Advertisement

Answer