Change in ovirt-engine[master]: db: drop redundant indexes from db

Martin Peřina has submitted this change and it was merged. ( https://gerrit.ovirt.org/80919 ) Change subject: db: drop redundant indexes from db ...................................................................... db: drop redundant indexes from db In Postgres a primary key implies a unique index implicitly. Therefor, there is no need to create an index explicitly that is identical to columns on which the PK is defined. This patch removes redundant indexes from the database. The duplicate indexes were found using the following query: SELECT pg_size_pretty(SUM(pg_relation_size(idx))::BIGINT) AS SIZE, (array_agg(idx))[1] AS idx1, (array_agg(idx))[2] AS idx2, (array_agg(idx))[3] AS idx3, (array_agg(idx))[4] AS idx4 FROM ( SELECT indexrelid::regclass AS idx, ( indrelid::text ||E'\n'|| indclass::text ||E'\n'|| indkey::text ||E'\n'|| COALESCE(indexprs::text,'')||E'\n' || COALESCE(indpred::text,'')) AS KEY FROM pg_index) sub GROUP BY KEY HAVING COUNT(*)>1 ORDER BY SUM(pg_relation_size(idx)) DESC; The results were: engine_1483889=# \d idx_vm_ovf_generations_vm_guid Index "public.idx_vm_ovf_generations_vm_guid" Column | Type | Definition ---------+------+------------ vm_guid | uuid | vm_guid btree, for table "public.vm_ovf_generations" engine_1483889=# \d vm_ovf_generations_pkey Index "public.vm_ovf_generations_pkey" Column | Type | Definition ---------+------+------------ vm_guid | uuid | vm_guid primary key, btree, for table "public.vm_ovf_generations" engine_1483889=# \d idx_engine_backup_log Index "public.idx_engine_backup_log" Column | Type | Definition ---------+--------------------------+------------ scope | character varying(64) | scope done_at | timestamp with time zone | done_at unique, btree, for table "public.engine_backup_log" engine_1483889=# \d engine_backup_log_pkey Index "public.engine_backup_log_pkey" Column | Type | Definition ---------+--------------------------+------------ scope | character varying(64) | scope done_at | timestamp with time zone | done_at primary key, btree, for table "public.engine_backup_log" engine_1483889=# \d idx_event_notification_hist Index "public.idx_event_notification_hist" Column | Type | Definition --------------+--------+-------------- audit_log_id | bigint | audit_log_id btree, for table "public.event_notification_hist" engine_1483889=# \d idx_event_notification_hist_audit_log_id Index "public.idx_event_notification_hist_audit_log_id" Column | Type | Definition --------------+--------+-------------- audit_log_id | bigint | audit_log_id btree, for table "public.event_notification_hist" engine_1483889=# \d idx_supported_cluster_features Index "public.idx_supported_cluster_features" Column | Type | Definition ------------+------+------------ cluster_id | uuid | cluster_id feature_id | uuid | feature_id unique, btree, for table "public.supported_cluster_features" engine_1483889=# \d pk_supported_cluster_features Index "public.pk_supported_cluster_features" Column | Type | Definition ------------+------+------------ cluster_id | uuid | cluster_id feature_id | uuid | feature_id primary key, btree, for table "public.supported_cluster_features" Change-Id: I7760ccf64af99b243a87e1344eb0d2b6caadaeb3 Signed-off-by: emesika <emesika(a)redhat.com&gt; --- M packaging/dbscripts/common_sp.sql A packaging/dbscripts/upgrade/04_02_0510_drop_redundant_indexes.sql 2 files changed, 20 insertions(+), 0 deletions(-) Approvals: Martin Peřina: Looks good to me, approved Eli Mesika: Verified Jenkins CI: Passed CI tests -- To view, visit https://gerrit.ovirt.org/80919 To unsubscribe, visit https://gerrit.ovirt.org/settings Gerrit-MessageType: merged Gerrit-Change-Id: I7760ccf64af99b243a87e1344eb0d2b6caadaeb3 Gerrit-PatchSet: 3 Gerrit-Project: ovirt-engine Gerrit-Branch: master Gerrit-Owner: Eli Mesika <emesika(a)redhat.com&gt; Gerrit-Reviewer: Arik Hadas <ahadas(a)redhat.com&gt; Gerrit-Reviewer: Eli Mesika <emesika(a)redhat.com&gt; Gerrit-Reviewer: Jenkins CI Gerrit-Reviewer: Martin Peřina <mperina(a)redhat.com&gt; Gerrit-Reviewer: Moti Asayag <masayag(a)redhat.com&gt; Gerrit-Reviewer: gerrit-hooks <automation(a)ovirt.org&gt;