That is just a sample data, we are going live in Jun and I don't have anything real so far. Right now it's 9.6 and it will be a latest stable available release on the date that we go live.
We have a query that is run almost each second and it's very important to squeeze every other ms out of it. The query is:
SELECT c0."id" FROM "campaign_jobs" AS c0 WHERE (((c0."status" = $1) AND NOT (c0."id" = ANY($2)))) OR ((c0."status" = $3) AND (c0."failed_at" > $4)) OR ((c0."status" = $5) AND (c0."started_at" < $6)) ORDER BY c0."priority" DESC, c0."times_failed" LIMIT $7 FOR UPDATE SKIP LOCKED
I see that query still went through the Seq Scan instead of Index Scan. Is it due to poorly crafted index or because of query structure? Is it possible to make this query faster?
An index on (priority desc, times_failed) should speed this up massively. Might want to include status at the end as well. However, your example data is not terribly realistic.