Queries with multiple SORTS can be very slow in Postgres

Products

• Clarity PPM SaaS

Issue/Introduction

Users may report very slow performance of some portlets, sometimes so bad that the page in the classic UI times out with an error.

Cause

Some queries may use a large sort/order by which exceeds the allotted / configured space for the "sort area".

This is the area used by Postgres to hold the sort while it is being performed.

The "work_mem" setting.

Resolution

Increase the work_mem setting with the DBA's guidance.

If increasing work_mem setting does not help with performance of the query we should check bloat table size which can be reclaim with running a full vacuum on specific table, note should work with DBA team to analyze it. Table bloat size can grow with frequent table update/delete/inserts.

Additional Information

To see the current settings, run this query as the schema user for clarity.

SELECT
  NAME,
  setting
 FROM
  pg_settings;