Job count by state query performance

[guenaelgonnord]

Hi,

on a heavy loaded database instance with millions of hangfire runned jobs, I think I found a better index on job count by state query :

SELECT
  "statename" "State",
  COUNT("id") "Count"
FROM
  "hangfire"."job"
WHERE
  "statename" IS NOT NULL
GROUP BY
  "statename"

We might use this one that is using original where clause + included column : create or replace index ix_hangfire_job_statename_is_not_null on job(statename) include (id) where statename is not null;

With that index, the query above is now performing an index only scan which performs better.

Do you think it could be a good option for your database init script ?

[azygis]

Would you be able to provide the differences in terms of execution plans? One without new index, one with, on that heavily-loaded database if possible? Would be nice if it's links to dalibo visualizer.

[guenaelgonnord]

Here are 2 screenshots from Query insights (Google CloudSQL PostgreSQL) :

More explain : https://explain.dalibo.com/plan/043bdcc9d5e5cf1a

[azygis]

I don't see why not, honestly. If it helps, it helps, while I personally can't see how it could regress anything. Would you be willing to create a PR for this new index?

[azygis]

It's been merged, but will be released later on when there are more changes. You're free to just continue using the same index as it will later be recreated with an update.