Bug 1614354

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)
~~~