Postgres Performance – Addressing Not Null Performance Issues

postgresql

According to Performance Insights in Amazon RDS, the following query is causing some high wait times in my postgresql database:

SELECT (1) AS "a"
FROM "employee_employee"
WHERE ("employee_employee"."year" = 2018
  AND "employee_employee"."jurisdiction_id" = 537
  AND "employee_employee"."ual" IS NOT NULL)
LIMIT 1

This query comes from the python Django ORM and is written like this:

context["hide_ual"] = not records.filter(ual__isnull=False).exists()

I have indexes built for year and jurisdiction_id fields. The ual field is a numeric type and is very often NULL. I'm tempted to create a separate way to identify if a jurisdiction even has ual associated with it, so I can stop constantly checking to see if ual values exist. Before I do that, is there a better way to improve speed within the database?

Best Answer

You could create a partial index:

CREATE INDEX ON employee_employee (year, jurisdiction_id) WHERE ual IS NOT NULL;
Related Question