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.
[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/
severity and priority set to medium. If it will demonstrate a real impact I'll raise it. Anyhow, it's an easy fix
Why is the bug on MODIFIED if one patch is still on POST?
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
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
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.
(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.
According to Comment 7, I created clone for aaa_jdbc Bug 1535899. But index on network table should be fixed within this BZ.
Target release should be placed once a package build is known to fix a issue. Since this bug is not modified, the target version has been reset. Please use target milestone to plan a fix for a oVirt release.
No missing indexes in engine database. verified in ovirt-engine-4.2.3.2-0.1.el7.noarch
This bugzilla is included in oVirt 4.2.3 release, published on May 4th 2018. Since the problem described in this bug report should be resolved in oVirt 4.2.3 release, it has been closed with a resolution of CURRENT RELEASE. If the solution does not work for you, please open a new bug report.