commit fda15dea837b66a6174b9677c794761e6786848f
author Hugo Arès <hugo.ares@ericsson.com> Thu Sep 07 15:11:24 2017 -0400
committer David Pursehouse <dpursehouse@collab.net> Mon Sep 11 23:52:26 2017 +0000
tree d160ca23bc747b481d473b3256357316496ba0b7
parent 4b12bb0979b18956bf887d7b369c8da3f2b02f17

Fix perfomance issue when clearing reviewed flag for a patchset

Review flags are stored in a single table with 4 columns: account_id,
change_id, patch_set_id and file_name. The primary key is composed of
the 4 columns in that order: account_id, change_id, patch_set_id and
file_name. Most of the database servers create implicitly an index for
the primary key, at least H2, MySQL and PostgreSQL do.

Review flags table is likely to have a lot of rows since rows are only
deleted when user un-ticks a reviewed file or when a patchset is deleted.
This is when having the right indices are getting important. Example,
deleting all review flags of a specific change's patchset takes 22
seconds in a Postgres server with a 32 millions rows table. Creating an
index on the change_id and patch_set_id columns brings the deletion time
to 600ms.

Instead of creating a new index, change the order of the primary key and
thus the implicit index so it can be used by all queries.

If an index is created for columns (a,b,c,d) then queries that involve
any subset of the leftmost columns will leverage the index, i.e. queries
with condition on (a) or (a,b) or (a,b,c) and obviously (a,b,c,d).

In JdbcAccountPatchReviewStore implementation, the following conditions
are used in the various queries:

1-account_id, change_id, patch_set_id and file_name
2-account_id, change_id, patch_set_id
3-change_id, patch_set_id

The original primary key(index) was created on (account_id, change_id,
patch_set_id, file_name) which can be used by queries with condition 1
and 2 but not 3.

Changing the primary key to (change_id, patch_set_id, account_id,
file_name) creates an implicit index that will be used by queries with
condition 1, 2 and 3.

Installations which already upgraded to 2.13 or 2.14 will have to
manually drop the primary key to recreate it with proper order:

ALTER TABLE account_patch_reviews
DROP CONSTRAINT primary_key_account_patch_reviews;

ALTER TABLE account_patch_reviews
ADD CONSTRAINT primary_key_account_patch_reviews
PRIMARY KEY (change_id, patch_set_id, account_id, file_name);

Change-Id: Ibb68926e056e0172696e8f9248450bdff8401d89