Plugins » Katello

Cloned from https://bugzilla.redhat.com/show_bug.cgi?id=1601502

Description of problem:

this query via the API:

POST /api/v2/hosts/bulk/installable_errata

{"organization_id": 1, "included":{"search": "lifecycle_environment_id=5 and content_view_id=4"},"per_page":10,"page":1}

Is simply hanging - I mean I'm sure it's doing something somewhere! I.e. Return me all errata that is installable and in lifecycle environment 5 and content view 4.

It doing something as I see at least one of these queries:

explain analyze SELECT "katello_errata".* FROM "katello_errata" WHERE "katello_errata"."id" IN (SELECT "katello_errata"."id" FROM "katello_errata" INNER JOIN "katello_content_facet_errata" ON "katello_content_facet_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 AND katello_content_facet_repositories.repository_id = host_repo_errata.repository_id WHERE "katello_content_facet_repositories"."content_facet_id" IN (SELECT katello_content_facets.id FROM "hosts" INNER JOIN "katello_content_facets" ON "katello_content_facets"."host_id" = "hosts"."id" WHERE "hosts"."type" IN ('Host::Managed') AND "hosts"."organization_id" = 1 AND "hosts"."organization_id" = 1 AND ((("katello_content_facets"."lifecycle_environment_id" = '5') AND ("katello_content_facets"."content_view_id" = '4'))) ORDER BY "hosts"."name" ASC)) ORDER BY "katello_errata"."updated" DESC, katello_errata.id DESC LIMIT 10 OFFSET 0;

With this plan:

Limit (cost=4140316.55..4140316.57 rows=10 width=707) (actual time=80178.279..80178.281 rows=10 loops=1)
-> Sort (cost=4140316.55..4140322.01 rows=2184 width=707) (actual time=80178.277..80178.277 rows=10 loops=1)
Sort Key: public.katello_errata.updated, public.katello_errata.id
Sort Method: top-N heapsort Memory: 39kB
-> Hash Join (cost=4137429.84..4140269.35 rows=2184 width=707) (actual time=80165.916..80176.658 rows=1355 loops=1)
Hash Cond: (public.katello_errata.id = katello_content_facet_errata.erratum_id)
-> Seq Scan on katello_errata (cost=0.00..2381.04 rows=19304 width=707) (actual time=0.001..5.150 rows=19470 loops=1)
-> Hash (cost=4137188.54..4137188.54 rows=19304 width=12) (actual time=80165.828..80165.828 rows=1355 loops=1)
Buckets: 2048 Batches: 1 Memory Usage: 59kB
-> HashAggregate (cost=4136995.50..4137188.54 rows=19304 width=12) (actual time=80164.640..80165.263 rows=1355 loops=1)
-> Hash Join (cost=3882451.92..4132263.45 rows=1892823 width=12) (actual time=50804.859..79964.381 rows=717261 loops=1)
Hash Cond: (katello_content_facet_errata.erratum_id = public.katello_errata.id)
-> Merge Join (cost=3879829.58..4084686.56 rows=1892823 width=8) (actual time=50778.411..79683.635 rows=717261 loops=1)
Merge Cond: ((katello_content_facet_errata.erratum_id = host_repo_errata.erratum_id) AND (katello_content_facet_repositories.repository_id = host_repo_errata.repository_id))
-> Sort (cost=3620587.49..3676906.83 rows=22527735 width=8) (actual time=48726.563..70318.237 rows=20931399 loops=1)
Sort Key: katello_content_facet_errata.erratum_id, katello_content_facet_repositories.repository_id
Sort Method: external merge Disk: 367760kB
-> Hash Join (cost=15119.21..561368.95 rows=22527735 width=8) (actual time=481.745..9159.776 rows=20931399 loops=1)
Hash Cond: (katello_content_facet_errata.content_facet_id = katello_content_facet_repositories.content_facet_id)
-> Seq Scan on katello_content_facet_errata (cost=0.00..120340.49 rows=7295749 width=8) (actual time=0.016..1435.634 rows=7754727 loops=1)
-> Hash (cost=14380.76..14380.76 rows=59076 width=12) (actual time=481.397..481.397 rows=55837 loops=1)
Buckets: 8192 Batches: 1 Memory Usage: 2400kB
-> Hash Semi Join (cost=8474.60..14380.76 rows=59076 width=12) (actual time=173.991..434.825 rows=55837 loops=1)
Hash Cond: (katello_content_facet_repositories.content_facet_id = "ANY_subquery".id)
-> Seq Scan on katello_content_facet_repositories (cost=0.00..4292.51 rows=252351 width=8) (actual time=0.014..87.519 rows=254143 loops=1)
-> Hash (cost=8418.63..8418.63 rows=4477 width=4) (actual time=173.926..173.926 rows=4519 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 159kB
-> Subquery Scan on "ANY_subquery" (cost=8362.67..8418.63 rows=4477 width=4) (actual time=165.233..172.578 rows=4519 loops=1)
-> Sort (cost=8362.67..8373.86 rows=4477 width=32) (actual time=165.231..166.000 rows=4519 loops=1)
Sort Key: hosts.name
Sort Method: quicksort Memory: 546kB
-> Hash Join (cost=852.73..8091.18 rows=4477 width=32) (actual time=13.473..93.355 rows=4519 loops=1)
Hash Cond: (hosts.id = katello_content_facets.host_id)
-> Seq Scan on hosts (cost=0.00..7000.33 rows=25780 width=32) (actual time=0.028..69.453 rows=25783 loops=1)
Filter: (((type)::text = 'Host::Managed'::text) AND (organization_id = 1))
Rows Removed by Filter: 42
-> Hash (cost=796.68..796.68 rows=4484 width=8) (actual time=13.432..13.432 rows=4519 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 177kB
-> Seq Scan on katello_content_facets (cost=0.00..796.68 rows=4484 width=8) (actual time=0.017..12.049 rows=4519 loops=1)
Filter: ((lifecycle_environment_id = 5) AND (content_view_id = 4))
Rows Removed by Filter: 15394
-> Materialize (cost=259239.76..268665.00 rows=1885049 width=8) (actual time=2047.972..4159.879 rows=2665075 loops=1)
-> Sort (cost=259239.76..263952.38 rows=1885049 width=8) (actual time=2047.920..3816.610 rows=1949179 loops=1)
Sort Key: host_repo_errata.erratum_id, host_repo_errata.repository_id
Sort Method: external merge Disk: 34248kB
-> Seq Scan on katello_repository_errata host_repo_errata (cost=0.00..36985.49 rows=1885049 width=8) (actual time=0.016..377.497 rows=1949184 loops=1)
-> Hash (cost=2381.04..2381.04 rows=19304 width=4) (actual time=26.310..26.310 rows=19470 loops=1)
Buckets: 2048 Batches: 1 Memory Usage: 685kB
-> Seq Scan on katello_errata (cost=0.00..2381.04 rows=19304 width=4) (actual time=0.001..20.918 rows=19470 loops=1)
Total runtime: 80321.771 ms
(50 rows)