Description of problem: While listing the applicable errata for hosts, it takes a long time (more than 1 hour). Only this task takes long time irrespective of any host. #time curl -u admin -k -i 'https://satellite.example.com/katello/api/v2/packages?host_id=5905&packages_restrict_upgradable=true&page=1&paged=true&per_page=20' Status: 200 OK [...] real 66m39.589s user 0m0.140s sys 0m0.143s Or Content Hosts -> 'click on hostname' -> Packages -> 'Applicable Packages' Version-Release number of selected component (if applicable): Red Hat Satellite 6.3.2. How reproducible: Using satellite backup. Steps to Reproduce: 1. Update configuration # cd /tmp/backup/satellite-backup/ # tar -xvzf config_files.tar.gz # cd config_files/ # rm -rf var/lib/qpidd/.qpidd/ # vi etc/foreman-installer/scenarios.d/satellite-answers.yaml and set --> ipa_authentication: false # tar -zcvf config_files.tar.gz ./ # cp config_files.tar.gz /tmp/backup/satellite-backup/ 2. Restore satellite backup # satellite-restore /tmp/backup/satellite-backup/ // if above fails for qpid # mv /etc/pki/katello/nssdb{,.bak} # satellite-installer --disable-system-checks Actual results: Listing of errata should take time only if the applicable errata list is too long. In customer's case, it takes long time to list applicable errata for any/all host. Expected results: Listing of errata task should not take much time. Additional info: 1. Satellite Upgrade and migration is successful ~~~ #foreman-rake db:migrate:status --> Returns all migration are up ~~~ 2. After making API Call SQL query takes a long time to complete ~~~ foreman=# SELECT DATE_TRUNC('second',NOW()-query_start) AS age, client_port, query FROM pg_stat_activity WHERE state != '<IDLE>' AND NOW() - query_start > '00:00:10' ORDER BY age DESC; age | client_port | query 00:08:55 | -1 | LISTEN "world:52160980-5c89-4673-bb8c-2e88cf04fb2b" 01:59:43 | -1 | SELECT DISTINCT COUNT(DISTINCT "katello_rpms"."id") FROM "katello_rpms" INNER JOIN "katello_content_facet_applicable_rpms" ON "katello_content_facet_applicable_rpms"."rpm_id" = "katello _rpms"."id" INNER JOIN "katello_repository_rpms" ON "katello_repository_rpms"."rpm_id" = "katello_rpms"."id" INNER JOIN "katello_content_facet_applicable_rpms" "content_facet_applicable_rpms_katello_rpms_join" O N "content_facet_applicable_rpms_katello_rpms_join"."rpm_id" = "katello_rpms"."id" INNER JOIN "katello_content_facets" ON "katello_content_facets"."id" = "content_facet_applicable_rpms_katello_rpms_join"."conten t_facet_id" INNER JOIN katello_content_facet_repositories on katello_content_facet_repositories.content_facet_id = katello_content_facet_applicable_rpms.content_facet_id INNER JOIN katello_repository_rpm s AS host_repo_rpm ON host_repo_rpm.rpm_id = katello_rpms.id WHERE "katello_rpms"."id" IN (SELECT "katello_repository_rpms"."rpm_id" FROM "katello_repository_rpms" WHERE "katello_repository_rpms" ~~~ 3. There are 5 million record in katello_content_facet_applicable_rpms foreman=# select count(*) from katello_content_facet_applicable_rpms; count --------- 5454172 (1 row) 4. Postgres database is mostly filled with errors as follows: ~~~ ERROR: duplicate key value violates unique constraint "katello_content_facet_repository_rid_caid" DETAIL: Key (repository_id, content_facet_id)=(649, 5093) already exists. ~~~ 5. Tried to vacuum the tables but didn't worked. ~~~ VACUUM ANALYZE katello_content_facet_applicable_rpms; VACUUM ANALYZE katello_rpms; VACUUM ANALYZE katello_repository_rpms; ~~~ 6. There is no duplicate record in katello_content_facet_applicable_rpms ~~~ SELECT content_facet_id, rpm_id, COUNT(content_facet_id) as c FROM katello_content_facet_applicable_rpms GROUP BY content_facet_id, rpm_id ORDER BY c; ~~~ 7. All indexes are in place ~~~ foreman=# \d katello_content_facet_applicable_rpms; Table "public.katello_content_facet_applicable_rpms" Column | Type | Modifiers ------------------+---------+------------------------------------------------------------------------------------ id | integer | not null default nextval('katello_content_facet_applicable_rpms_id_seq'::regclass) content_facet_id | integer | not null rpm_id | integer | not null Indexes: "katello_content_facet_applicable_rpms_pkey" PRIMARY KEY, btree (id) "katello_content_facet_rpm_rid_cfid" UNIQUE, btree (rpm_id, content_facet_id) "index_k_content_facet_applicable_rpms_on_content_facet_id" btree (content_facet_id) ~~~
Hi Diksha, do you have an internal reproducer environment that the team could access for debugging?
*** This bug has been marked as a duplicate of bug 1608597 ***