2017-01-30 07:37:49 EST LOG:  duration: 122807.009 ms  execute <unnamed>: SELECT  "katello_errata".* FROM "katello_errata"  WHERE "katello_errata"."id" IN (SELECT DISTINCT "katello_errata"."id" FROM "katello_errata" INNER JOIN "katello_repository_errata" ON "katello_repository_errata"."erratum_id" = "katello_errata"."id" INNER JOIN "katello_content_facet_errata" ON "katello_content_facet_errata"."erratum_id" = "katello_errata"."id" INNER JOIN "katello_content_facets" ON "katello_content_facets"."id" = "katello_content_facet_errata"."content_facet_id" INNER JOIN "katello_content_facet_errata" "content_facet_errata_katello_errata" ON "content_facet_errata_katello_errata"."erratum_id" = "katello_errata"."id" INNER JOIN katello_content_facet_repositories on         katello_content_facet_repositories.content_facet_id = katello_content_facet_errata.content_facet_id INNER JOIN katello_repository_errata AS host_repo_errata ON           host_repo_errata.erratum_id = katello_errata.id WHERE "katello_repository_errata"."repository_id" IN (SELECT "katello_repositories"."id" FROM "katello_repositories"  WHERE (katello_repositories.id in (11,3,1,2,4,5,7,8,6,9,10) or katello_repositories.id in (5,6) or katello_repositories.id in (11,6,5,4,3,2,1,10,9,8,7))) AND "katello_repository_errata"."repository_id" IN (SELECT "katello_repositories"."id" FROM "katello_repositories"  WHERE (katello_repositories.id in (11,3,1,2,4,5,7,8,6,9,10) or katello_repositories.id in (5,6) or katello_repositories.id in (11,6,5,4,3,2,1,10,9,8,7)) AND "katello_repositories"."environment_id" IN (1)) AND "katello_content_facets"."host_id" IN (SELECT "hosts"."id" FROM "hosts"  WHERE "hosts"."type" IN ('Host::Managed')) AND (katello_content_facet_repositories.repository_id = host_repo_errata.repository_id) AND "katello_content_facets"."host_id" IN (15124, [...], 8844, 1))  ORDER BY "katello_errata"."updated" DESC, katello_errata.id DESC LIMIT 50 OFFSET 0
2017-01-30 07:42:16 EST LOG:  duration: 1016.397 ms  statement: COMMIT

Maybe this is related or even duplicate of bug 1417437

This does not look like a duplicate. I hit the same issue earlier with this query, I believe it's specific to checking the "installable" checbox.

one cheap win is adding this index:
 CREATE INDEX katello_content_facet_errata_ca_id_idx ON katello_content_facet_errata (content_facet_id);
which elliminates a full scan on katello_content_facet_errata while
  INNER JOIN "katello_content_facets" ON "katello_content_facets"."id" = "katello_content_facet_errata"."content_facet_id"

FTR, the worst offender (according to an explain analyze run though https://explain.depesz.com/) is
  AND "katello_content_facets"."host_id" IN 
   (select "hosts"."id" FROM "hosts"  WHERE "hosts"."type" IN ('Host::Managed'))
which itself is "fine", but given we run this thousands of times is kinda yuk.

actually, why are we even checking this? there will be no errata for Host::Hypervisor anyways...

/opt/theforeman/tfm/root/usr/share/gems/gems/katello-3.0.0.94/app/models/katello/erratum.rb :

    def self.installable_for_hosts(hosts = nil)
      query = Katello::Erratum.joins(:content_facet_errata).joins(:repository_errata).
        joins("INNER JOIN #{Katello::ContentFacetRepository.table_name} on \
        #{Katello::ContentFacetRepository.table_name}.content_facet_id = #{Katello::ContentFacetErratum.table_name}.content_facet_id").
        joins("INNER JOIN #{Katello::RepositoryErratum.table_name} AS host_repo_errata ON \
          host_repo_errata.erratum_id = #{Katello::Erratum.table_name}.id").
        where("#{Katello::ContentFacetRepository.table_name}.repository_id = host_repo_errata.repository_id")

      query = query.joins(:content_facets).where("#{Katello::Host::ContentFacet.table_name}.host_id" => [hosts.map(&:id)]) if hosts
      query.uniq
    end

That's the problematic query.

Created redmine issue http://projects.theforeman.org/issues/18376 from this bug

Upstream bug assigned to paji

This updated query improve timing a lot.  8 mins to 4.6 sec
Thanks

HOTFIX INSTRUCTIONS - SATELLITE 6.2.7 (RHEL 7)

Patch to improve the performance of Installable Errata queries

This patch addresses issues specified in https://bugzilla.redhat.com/show_bug.cgi?id=1417642 and https://bugzilla.redhat.com/show_bug.cgi?id=1377725 

Install instructions: 
  
1) Download the attached patch  to your Satellite Server.

2) Stop services

katello-service stop

3) Install patch
cd `rpm -ql tfm-rubygem-katello | head -n 1`
patch -p1 < ~/Bz-1377725-1417642.patch

4) katello-service start

5) continue with normal operations

Moving this bug to POST for triage into Satellite 6 since the upstream issue http://projects.theforeman.org/issues/18376 has been resolved.

*** Bug 1425800 has been marked as a duplicate of this bug. ***

*** Bug 1417437 has been marked as a duplicate of this bug. ***

Please add verifications steps for this bug to help QE verify

Verification steps

1) Register hosts at large scale. 
2) content -> errata 
3) Select an errata which has large Content Host Counts -> Content Hosts
4) "Installable" checkbox checked

check how long it takes to list applicable hosts for the specific errata

--Pradeep

Verified in Satellite 6.2.9 Snap 4

On a system with just under 6k content hosts, most of which had install-able errata, the page loaded in an average of 4.5s (10 load average). See attached screenshot for verification.

Since the problem described in this bug report should be
resolved in a recent advisory, it has been closed with a
resolution of ERRATA.

For information on the advisory, and where to find the updated
files, follow the link below.

If the solution does not work for you, open a new bug report.

https://access.redhat.com/errata/RHBA-2017:1191