Issue Summary

High DB CPU utilization is observed in PostgreSQL database after upgrading, offending query:
 

select internalre0_1_.created_timestamp as col_0_0_  from sta_repo_activity internalre0_ inner join sta_activity internalre0_1_ on internalre0_.activity_id=internalre0_1_.id left outer join sta_cmt_disc_activity internalre0_2_ on internalre0_.activity_id=internalre0_2_.activity_id left outer join bb_cmt_disc_comment_activity internalre0_3_ on internalre0_.activity_id=internalre0_3_.activity_id left outer join sta_repo_created_activity internalre0_4_ on internalre0_.activity_id=internalre0_4_.activity_id left outer join sta_repo_push_activity internalre0_5_ on internalre0_.activity_id=internalre0_5_.activity_id left outer join sta_repo_updated_activity internalre0_6_ on internalre0_.activity_id=internalre0_6_.activity_id  where internalre0_.repository_id=$1 and (internalre0_5_.trigger_id is null or internalre0_5_.trigger_id<>$2)  order by internalre0_1_.created_timestamp DESC limit $3

The problem is especially noticeable immediately after upgrade

This problem, specifically the above query, is related to a change made as part of addressing BSERV-14405. That issue saw a number of changes made, with the one that added the above query made in 8.9.15, 8.19.4, and 9.0.0. The previous iteration of the above query was known to be slow, and the new version of it (as shown above) is believed to be slower, as least on PostgreSQL.

The above query if related to a background task, so it may not directly impact user visible performance. However the increase load on the database, especially if the database resource constrained, means that there may be an indirect impact to user visible performance.

Steps to Reproduce

-

Expected Results

-

Actual Results

High DB CPU usage observed, offending query

select internalre0_1_.created_timestamp as col_0_0_  from sta_repo_activity internalre0_ inner join sta_activity internalre0_1_ on internalre0_.activity_id=internalre0_1_.id left outer join sta_cmt_disc_activity internalre0_2_ on internalre0_.activity_id=internalre0_2_.activity_id left outer join bb_cmt_disc_comment_activity internalre0_3_ on internalre0_.activity_id=internalre0_3_.activity_id left outer join sta_repo_created_activity internalre0_4_ on internalre0_.activity_id=internalre0_4_.activity_id left outer join sta_repo_push_activity internalre0_5_ on internalre0_.activity_id=internalre0_5_.activity_id left outer join sta_repo_updated_activity internalre0_6_ on internalre0_.activity_id=internalre0_6_.activity_id  where internalre0_.repository_id=$1 and (internalre0_5_.trigger_id is null or internalre0_5_.trigger_id<>$2)  order by internalre0_1_.created_timestamp DESC limit $3

Workaround

There are no recommended workarounds at the moment.