Bug 1535899

+++ This bug was initially created as a clone of Bug #1512412 +++

Description of problem:

An analysis using pgcluu[1] resulted a handful of missing indexes on 28 of our foreign keys.

Since our schema is read and update mostly, and tables are small the expected cost of adding the indexes is negligible.
 

Steps to Reproduce:
1. run this query [2] or run the CI OST performance suite [2] and check the pgcluu report [3] or just run pgcluu against the engine db.

Actual results:
the output contains 28 missing indexes

Expected results:
0 missing indexes. 

Additional info:
May improve performance of some queries on views involving those foreign keys.

An integration test will be added to check that the report contains zero results.

--- Additional comment from Roy Golan on 2017-11-13 02:38:17 EST ---

[1] pgcluu project - https://github.com/darold/pgcluu

[2] sql script to generate the report - https://gist.github.com/rgolangh/2a195f0822ba6b68daf52f44ef33fda4

[3] CI performance suite - http://jenkins.ovirt.org/job/ovirt-system-tests_master_check-patch-el7-x86_64/2177/artifact/exported-artifacts/performance-suite-master__logs/test_logs/performance-suite-master/post-1000_analyze_postgresql.py/lago-performance-suite-master-engine/_var_opt_rh_rh-postgresql95_lib_pgsql_data_pg_log/pgcluu/

--- Additional comment from Roy Golan on 2017-11-13 02:46:51 EST ---

severity and priority set to medium. If it will demonstrate a real impact I'll raise it. Anyhow, it's an easy fix

--- Additional comment from RHV Bugzilla Automation and Verification Bot on 2017-12-06 10:58:01 EST ---

Why is the bug on MODIFIED if one patch is still on POST?

--- Additional comment from RHV Bugzilla Automation and Verification Bot on 2017-12-06 11:18:07 EST ---

INFO: Bug status wasn't changed from MODIFIED to ON_QA due to the following reason:

[Open patch attached]

For more info please contact: infra

--- Additional comment from Roy Golan on 2017-12-28 12:20:32 EST ---

The extra patch [1] is a nice to have but not cardinal to bug. I removed it from the bug and I'm moving this back to ON_QA

[1] https://gerrit.ovirt.org/c/84012/3

--- Additional comment from Lucie Leistnerova on 2018-01-18 03:26:17 EST ---

pgcluu report shows other missing indexes:
Table 	Missing index
network	CREATE INDEX CONCURRENTLY idx_network_provider_physical_network_id ON network (provider_physical_network_id)
group_groups	CREATE INDEX CONCURRENTLY idx_group_groups_group_id ON aaa_jdbc.group_groups (group_id)
group_groups	CREATE INDEX CONCURRENTLY idx_group_groups_in_group_id ON aaa_jdbc.group_groups (in_group_id)
user_password_history	CREATE INDEX CONCURRENTLY idx_user_password_history_user_id ON aaa_jdbc.user_password_history (user_id)
user_groups	CREATE INDEX CONCURRENTLY idx_user_groups_user_id ON aaa_jdbc.user_groups (user_id)
user_groups	CREATE INDEX CONCURRENTLY idx_user_groups_in_group_id ON aaa_jdbc.user_groups (in_group_id)
group_attributes	CREATE INDEX CONCURRENTLY idx_group_attributes_group_id ON aaa_jdbc.group_attributes (group_id)
failed_logins	CREATE INDEX CONCURRENTLY idx_failed_logins_user_id ON aaa_jdbc.failed_logins (user_id)
user_attributes	CREATE INDEX CONCURRENTLY idx_user_attributes_user_id ON aaa_jdbc.user_attributes (user_id) 

Tested in ovirt-engine-4.2.1.1-0.1.el7.noarch

Should this indexes be also part of this BZ or we create new one? Except of one are the indexes in aaa_jdbc schema.

--- Additional comment from Martin Perina on 2018-01-18 03:32:42 EST ---

(In reply to Lucie Leistnerova from comment #6)
> group_groups	CREATE INDEX CONCURRENTLY idx_group_groups_group_id ON
> aaa_jdbc.group_groups (group_id)
> group_groups	CREATE INDEX CONCURRENTLY idx_group_groups_in_group_id ON
> aaa_jdbc.group_groups (in_group_id)
> user_groups	CREATE INDEX CONCURRENTLY idx_user_groups_user_id ON
> aaa_jdbc.user_groups (user_id)
> user_groups	CREATE INDEX CONCURRENTLY idx_user_groups_in_group_id ON
> aaa_jdbc.user_groups (in_group_id)
> group_attributes	CREATE INDEX CONCURRENTLY idx_group_attributes_group_id ON
> aaa_jdbc.group_attributes (group_id)
> failed_logins	CREATE INDEX CONCURRENTLY idx_failed_logins_user_id ON
> aaa_jdbc.failed_logins (user_id)
> user_attributes	CREATE INDEX CONCURRENTLY idx_user_attributes_user_id ON
> aaa_jdbc.user_attributes (user_id) 

All of above indexes are part of merged https://gerrit.ovirt.org/85100 but there was no new release of ovirt-engine-extension-aaa-jdbc, so that's why those indexes are still missing.