Changing the join strategy to improve performance of the PostgreSQL database

We’ve noticed that instances of Automation for Jira with a large number of entries in the audit log might encounter performance issues after upgrading to Automation for Jira 7.3. This problem occurs only on PostgreSQL databases.

Context

The performance issues stem from some queries using the hash join strategy instead of the merge join strategy, which is caused by changing the data types in Automation for Jira 7.3. If the hash tables are too large and can’t be loaded into memory because of the working memory limits, the database writes them to temporary disk files. This causes DB Write IOPS to spike and slows down other queries, which affects your automation rules.

Extra information:

• Related issue on our public Jira instance

• Join strategies in PostgreSQL

Investigation

To confirm this problem, check which join strategy is used in your database:

1. Run the following query on your database: 

   explain analyse select
       "AO_589059_AUDIT_ITEM_COMP_CGE"."AUDIT_ITEM_ID",
       "AO_589059_AUDIT_ITEM_COMP_CGE"."COMPONENT_NAME_KEY",
       "AO_589059_AUDIT_ITEM_COMP_CGE"."COMPONENT",
       "AO_589059_AUDIT_ITEM_COMP_CGE"."COMPONENT_ID",
       "AO_589059_AUDIT_ITEM_COMP_CGE"."DURATION",
       "AO_589059_AUDIT_ITEM_COMP_CGE"."ID",
       "AO_589059_AUDIT_ITEM_COMP_CGE"."OPTIMISED_IDS",
       "AO_589059_AUDIT_ITEM_COMP_CGE"."START_TIME",
       "AO_589059_AUDIT_ITEM_CGE_ITEM"."AUDIT_ITEM_COMPONENT_CHANGE_ID",
       "AO_589059_AUDIT_ITEM_CGE_ITEM"."AUDIT_ITEM_ID",
       "AO_589059_AUDIT_ITEM_CGE_ITEM"."CHANGE_FROM",
       "AO_589059_AUDIT_ITEM_CGE_ITEM"."CHANGE_TO",
       "AO_589059_AUDIT_ITEM_CGE_ITEM"."FIELD_NAME",
       "AO_589059_AUDIT_ITEM_CGE_ITEM"."ID",
       "AO_589059_AUDIT_ITEM_CGE_ITEM"."MESSAGE"
   from "public"."AO_589059_AUDIT_ITEM_COMP_CGE" "AO_589059_AUDIT_ITEM_COMP_CGE"
   left join "public"."AO_589059_AUDIT_ITEM_CGE_ITEM" "AO_589059_AUDIT_ITEM_CGE_ITEM"
   on "AO_589059_AUDIT_ITEM_COMP_CGE"."ID" = "AO_589059_AUDIT_ITEM_CGE_ITEM"."AUDIT_ITEM_COMPONENT_CHANGE_ID"
   where "AO_589059_AUDIT_ITEM_COMP_CGE"."AUDIT_ITEM_ID" = 10;

Result

If the strategy is:

• merge join: you’re using the correct strategy. If you’re still unhappy with performance, check Further steps below.

• hash join: you need to change the strategy. Follow the steps from Solution below.

• nested loop join: you need to change the strategy. Follow the steps from Solution below.

Additional details about join strategies could be found in the PostgreSQL documentation.

Solution

To change the strategy to merge join, run the following commands to collect statistics about the problematic database tables. Based on these statistics, the query planner will determine the most efficient strategy and change it:

1. Run ANALYZE VERBOSE "AO_589059_AUDIT_ITEM_COMP_CGE";
2. Run ANALYZE VERBOSE "AO_589059_AUDIT_ITEM_CGE_ITEM";
3. Run the query shown in Investigation to confirm that the strategy has changed.
4. Confirm that the DB Write IOPS has dropped and performance is restored. Additionally, you can check the steps in Further steps section below.

Further steps

Increasing the working memory

If the performance hasn’t improved, you can increase the working memory. This will allow the database to load more into the memory and not fallback into writing the tables into temporary disk files. In this example, we’ve set it to 16MB but you might need to increase it further and monitor the outcome:

SET work_mem = '16MB';