ryandotsmith/queue_classic — pull request #60 by jsuchal (2012-04-24 11:11:39+00:00)
Title: Index optimizations

In merge with pull request #59 primary key index definition on jobs table was dropped causing serious performance regression. This commit adds it back.

Another index is added to optimize query that polls unlocked jobs from a named queue. It is done by adding a composite index on (q_name, id) columns that yields simple index scan for this query. Since we use pg we can restrict only to columns that are not locked using a partial index making this query even faster and index smaller (especially for queues with many workers/locked jobs).

Here are benchmark results using 2 runs on benchmark.rb file.

Baseline:

user system total real
5 workers 6.310000 1.150000 7.460000 ( 82.541974)
5 workers 5.900000 0.840000 6.740000 ( 76.178325)

With primary key:
user system total real
5 workers 5.960000 0.910000 6.870000 ( 34.021094)
5 workers 6.080000 1.010000 7.090000 ( 34.210940)

With partial composite key:
user system total real
5 workers 5.590000 0.910000 6.500000 ( 31.838356)
5 workers 5.470000 0.910000 6.380000 ( 31.238608)

The difference between the latter two is quite small since benchmark.rb does use only one (default) queue which makes the composite key poorly selective. However the more named queues you have the more you should benefit from this optimization.