Issue 2256: All -> Merged page loads too slowly (~30 seconds) and generates many (~1000) queries to the database
Status:  New
Owner: ----
Reported by presich....@gmail.com, Nov 14, 2013
Affected Version: 2.5
DB: Postgresql 9.1
Tomcat 7

What steps will reproduce the problem?
1. Give user access to one project with 1 change or remove read access from any projects
2. Open page All -> Merged

What is the expected output? 
Nothing in few milliseconds.

What do you see instead?
Nothing BUT in 20-30 seconds. This search generates about 1000 queries to the database. Seems too much for such search)

Please provide any additional information below.

Hello guys,
I have similar situation..could you please advise if anything can be changed to improve gerrit query performance for our instance.

Environment: Gerrit 2.5, Postgresql 9.1, tomcat 7, Ubuntu 12.04.

So, I have an account Test Developer.
1) I give him Read access to one project with 1 Change, then go to page: All -> Merged, Gerrit generates ~1000 queries to the Database, so the page loads ~20-30 seconds:
SELECT T.change_key,T.created_on,T.last_updated_on,T.sort_key,T.owner_account_id,T.dest_project_name,T.dest_branch_name,T.open,T.status,T.nbr_patch_sets,T.current_patch_set_id,T.subject,T.topic,T.last_sha1_merge_tested,T.mergeable,T.row_version,T.change_id FROM changes T WHERE T.open='N' AND T.status=$1 AND T.sort_key<$2 ORDER BY T.sort_key DESC","parameters: $1 = 'M', $2 = '0022f8bc00000007'

2) I remove any access for him, then go to page All -> Merged - Situation is the same, ~1000 queries, ~20 seconds to load the page

3) Then I give access to about 50 projects (with one from the first variant) - <10 queries, milliseconds to load a page.

As i can assume, that is a issue with gerrit logic, so, are there any ways to improve gerrit queries?

As workaround I found, that using Search like: status:merged project:MyProject is working quickly (because WHERE project='MyProject" statement adds to the query) BUT any OR statement in the search returns us to the sloooow queries.
Nov 14, 2013
#1 presich....@gmail.com
As Shawn replied in repo-discuss (http://tinyurl.com/m6ytafh):

Yes. :-) 

Upgrade to 2.8 and turn on the Lucene secondary index. 2.8 contains a 
better search engine, and has made improvements to reduce the number 
of database queries that are needed to filter changes for access 
control reasons.