Laravel database queue indexes

[ThomasJamesCrawford]

👋

We are using a database queue (postgres) for jobs with a long delay to free up redis memory.

This is the query it generates to pull available jobs.

select * from "jobs" where "queue" = ? and (("reserved_at" is null and "available_at" <= ?) or ("reserved_at" <= ?)) order by "id" asc limit 1 FOR UPDATE SKIP LOCKED;

The problem we have is we run into queries like this. The query planner seems to choose the pkey index because it expects there to be many more rows returned than there will be and it's estimating the cost to sort the results is higher than using the already sorted index.

 Limit  (cost=0.43..682.78 rows=1 width=1158) (actual time=1007.438..1007.439 rows=0 loops=1)
   ->  LockRows  (cost=0.43..471506.49 rows=691 width=1158) (actual time=1007.437..1007.438 rows=0 loops=1)
         ->  Index Scan using jobs_pkey on jobs  (cost=0.43..471437.39 rows=691 width=1158) (actual time=1007.436..1007.436 rows=0 loops=1)
               Filter: ((available_at <= 1641460384) AND (queue = 'delayed'::text))
               Rows Removed by Filter: 1502826
 Planning Time: 0.105 ms
 Execution Time: 1007.455 ms

This combination of order by "id" and limit 1 makes it really hard to get around this.

We have dropped the order by "id" asc from the query to get it consistently to use our indexes.

Potential ways to fix it could be adding more fields to the order by

select * from "jobs" where "queue" = ? and (("reserved_at" is null and "available_at" <= ?) or ("reserved_at" <= ?)) order by "available_at, "id" asc, "reserved_at" limit 1 FOR UPDATE SKIP LOCKED;

or perhaps there could be an easy way to remove the order by from the query?