Dequeue query cost is expensive when job queue table contains more than 100k items and causing 100% CPU Usage

[yongliu-mdsol]

Issue:

1. In the query below, with case statement by queue value, it scans the entire table to get the order.
2. Also, with the order by job id, it also scans the entire table to get first job in the queue

string fetchJobSqlTemplate = @" UPDATE """ + _options.SchemaName + @""".""jobqueue"" SET ""fetchedat"" = NOW() AT TIME ZONE 'UTC' WHERE ""id"" IN ( SELECT ""id"" FROM """ + _options.SchemaName + $@""".""jobqueue"" WHERE ""queue"" = ANY (@queues) AND ""fetchedat"" {{0}} ORDER BY CASE ""queue"" {string.Join("\n", queues.Select((q, i) => $"WHEN '{q}' THEN {i}"))} ELSE {queues.Length} END, ""fetchedat"",""jobid"" FOR UPDATE SKIP LOCKED LIMIT 1 ) RETURNING ""id"" AS ""Id"", ""jobid"" AS ""JobId"", ""queue"" AS ""Queue"", ""fetchedat"" AS ""FetchedAt""; ";
https://github.com/frankhommers/Hangfire.PostgreSql/blob/master/src/Hangfire.PostgreSql/PostgreSqlJobQueue.cs#L67-L82

The cost from execution plan with 500k jobs in the queue is 18679.06
Note:

It's not clear to me whether job queue ordered by index and job processing order by job id is an intentional feature for Hangfire.Postgres. I don't find any doc stating the feature.

As Hangfire.SqlServer, they resolve the performance issue by ordering queue by alphabetic order. In that case, we can do "order by queue".
In terms of order by job id, Hangfire.SqlServer also doesn't support that.
With both changes, the execution plan looks like this.

Although the change is affecting how it processes the jobs, I would suggest making it as an option(at least) for consumers who don't need to maintain order of jobs and want high throughput.

Hangfire documentation: https://docs.hangfire.io/en/latest/background-processing/configuring-queues.html
Hangfire.SqlServer code: https://github.com/HangfireIO/Hangfire/blob/7a199b05cb5099e5fad5727e6021e15f676f602f/src/Hangfire.SqlServer/SqlServerJobQueue.cs#L217-L221

[yongliu-mdsol]

CREATE INDEX jobqueue_queue_fetchat_jobId ON hangfire.jobqueue USING btree (queue asc, fetchedat asc nulls last, jobid asc)
With the index above, I am able to lower the cost down to less than 1.

[frankhommers]

Can you put this in the install scripts through a PR?

[yongliu-mdsol]

@frankhommers I actually have the code ready. I will push it up soon

[vytautask]

I've released it to nuget.org with version 1.8.0.