Performance degradation due to missing composite index on hangfire.set(key, id) in PostgreSQL

[sahapasci]

Problem Description

A common query on the hangfire.set table shows severe performance degradation on PostgreSQL (I tested in 17):

SELECT "value"
FROM "hangfire"."set"
WHERE "key" = 'console:848ac0fc791286418'
ORDER BY "id"
LIMIT 1 OFFSET 0;

Execution plan BEFORE index

Limit  (cost=0.43..233.35 rows=1 width=114) (actual time=1317.599..1317.600 rows=1 loops=1)
  Output: value, id
  Buffers: shared hit=1226557 read=183314 written=317
  ->  Index Scan using set_pkey on hangfire.set  (cost=0.43..365210.11 rows=1568 width=114) (actual time=1317.597..1317.598 rows=1 loops=1)
        Output: value, id
        Filter: (set.key = 'console:848ac0fc791286418'::text)
        Rows Removed by Filter: 5115528
        Buffers: shared hit=1226557 read=183314 written=317
Planning:
  Buffers: shared hit=59
Planning Time: 0.230 ms
Execution Time: 1317.611 ms

• Query takes approximately 1.3 seconds.
• Uses the primary key index on id.
• No suitable index for filtering on "key".
• Scans over 5 million rows, causing heavy IO and latency.

The cost settings are mostly at their default values, except for the following parameter which is set differently. This value is commonly preferred:

hangfire=# show random_page_cost;
 random_page_cost
------------------
 1.1
(1 row)

Index creation statement:

CREATE INDEX set_key_id_idx ON "hangfire"."set" (key, id);

Execution plan AFTER adding the index:

Limit  (cost=0.56..1.68 rows=1 width=114) (actual time=0.029..0.030 rows=1 loops=1)
  Output: value, id
  Buffers: shared hit=2 read=3
  ->  Index Scan using set_key_id_idx on hangfire.set  (cost=0.56..1763.99 rows=1576 width=114) (actual time=0.029..0.029 rows=1 loops=1)
        Output: value, id
        Index Cond: (set.key = 'console:848ac0fc791286418'::text)
        Buffers: shared hit=2 read=3
Planning:
  Buffers: shared hit=59
Planning Time: 0.213 ms
Execution Time: 0.037 ms

• Execution time dropped to ~30 microseconds.
• Efficient index scan filtering on "key" and ordered by "id".
• Greatly reduced buffer hits and disk reads.

Summary & Recommendation

• The current schema lacks a composite index on (key, id), which is critical for this common query pattern.
• Adding this composite index significantly improves query speed and resource usage on PostgreSQL.
• It is recommended to include the following index in the default schema migrations or documentation:

CREATE INDEX CONCURRENTLY set_key_id_idx ON "hangfire"."set" (key, id);

This will help prevent performance bottlenecks on large datasets when querying by key and ordering by id.

Please consider this addition for upcoming releases or schema updates.

Thank you!

(moved from: HangfireIO/Hangfire#2526)

[azygis]

Feel free to create a pull request with the update.