Plugins » Katello

The following query is running for 13-16 minutes, and the dashboard page doesn't load until it returns. This is foreman-1.11.1, and katello-3.0-rc4. This only started occurring after I subscribed a few hundred hosts to katello.

By removing the Errata widget from the dashboard, page load time returns to normal, again.

SELECT  DISTINCT "katello_errata".* FROM "katello_errata" INNER JOIN "katello_content_facet_errata" 
ON "katello_content_facet_errata"."erratum_id" = "katello_errata"."id" INNER JOIN "katello_content_facet_errata" "content_facet_errata_katello_errata_join" 
ON  "content_facet_errata_katello_errata_join"."erratum_id" = "katello_errata"."id" INNER JOIN "katello_content_facets" 
ON "katello_content_facets"."id" = "content_facet_errata_katello_errata_join"."content_facet_id" 
WHERE "katello_content_facets"."host_id" IN (SELECT "hosts"."id" FROM "hosts"  
WHERE  "hosts"."type" IN ('Host::Managed') AND "hosts"."organization_id" IN (3) AND "hosts"."location_id" IN (5))  ORDER BY updated desc LIMIT 6;

foreman=# explain analyze SELECT  DISTINCT "katello_errata".* FROM "katello_errata" INNER JOIN "katello_content_facet_errata" 
ON "katello_content_facet_errata"."erratum_id" = "katello_errata"."id" INNER JOIN "katello_content_facet_errata" "content_facet_errata_katello_errata_join" 
ON "content_facet_errata_katello_errata_join"."erratum_id" = "katello_errata"."id" INNER JOIN "katello_content_facets" 
ON "katello_content_facets"."id" = "content_facet_errata_katello_errata_join"."content_facet_id" WHERE "katello_content_facets"."host_id" IN (SELECT "hosts"."id" FROM "hosts"  
WHERE "hosts"."type" IN ('Host::Managed') AND "hosts"."organization_id" IN (3) AND "hosts"."location_id" IN (5))  ORDER BY updated desc LIMIT 6;
                                                                                                                                                                                       QUERY PLAN                                                                             
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit  (cost=15961014.67..15961818.32 rows=6 width=735) (actual time=1010919.241..1011170.119 rows=6 loops=1)
   ->  Unique  (cost=15961014.67..16304980.23 rows=2568 width=735) (actual time=1010919.240..1011170.114 rows=6 loops=1)
         ->  Sort  (cost=15961014.67..15983945.70 rows=9172415 width=735) (actual time=1010919.239..1011007.917 rows=267176 loops=1)
               Sort Key: katello_errata.updated, katello_errata.id, katello_errata.uuid, katello_errata.errata_id, katello_errata.created_at, katello_errata.updated_at, katello_errata.issued, 
katello_errata.errata_type, katello_errata.severity, katello_errata.title, 
katello_errata.solution, katello_errata.description, katello_errata.summary, katello_errata.reboot_suggested
               Sort Method: external sort  Disk: 2310896kB
               ->  Merge Join  (cost=2163.33..8529.19 rows=9172415 width=735) (actual time=9.073..1455.029 rows=3565030 loops=1)
                     Merge Cond: (katello_errata.id = katello_content_facet_errata.erratum_id)
                     ->  Merge Join  (cost=2158.42..2647.34 rows=20574 width=739) (actual time=9.037..38.261 rows=12824 loops=1)
                           Merge Cond: (katello_errata.id = content_facet_errata_katello_errata_join.erratum_id)
                           ->  Index Scan using katello_errata_pkey on katello_errata  (cost=0.00..187.44 rows=1284 width=735) (actual time=0.006..1.841 rows=1160 loops=1)
                           ->  Sort  (cost=2156.64..2208.08 rows=20574 width=4) (actual time=9.010..23.102 rows=12824 loops=1)
                                 Sort Key: content_facet_errata_katello_errata_join.erratum_id
                                 Sort Method: quicksort  Memory: 986kB
                                 ->  Hash Join  (cost=82.03..682.67 rows=20574 width=4) (actual time=0.968..6.424 rows=12824 loops=1)
                                       Hash Cond: (content_facet_errata_katello_errata_join.content_facet_id = katello_content_facets.id)
                                       ->  Seq Scan on katello_content_facet_errata content_facet_errata_katello_errata_join  (cost=0.00..317.74 rows=20574 width=8) (actual time=0.009..1.601 rows=12827 loops=1)
                                       ->  Hash  (cost=77.16..77.16 rows=390 width=4) (actual time=0.946..0.946 rows=388 loops=1)
                                             Buckets: 1024  Batches: 1  Memory Usage: 14kB
                                             ->  Hash Join  (cost=13.78..77.16 rows=390 width=4) (actual time=0.209..0.873 rows=388 loops=1)
                                                   Hash Cond: (hosts.id = katello_content_facets.host_id)
                                                   ->  Seq Scan on hosts  (cost=0.00..56.95 rows=680 width=4) (actual time=0.008..0.560 rows=406 loops=1)
                                                         Filter: (((type)::text = 'Host::Managed'::text) AND (organization_id = 3) AND (location_id = 5))
                                                         Rows Removed by Filter: 2
                                                   ->  Hash  (cost=8.90..8.90 rows=390 width=8) (actual time=0.189..0.189 rows=390 loops=1)
                                                         Buckets: 1024  Batches: 1  Memory Usage: 16kB
                                                         ->  Seq Scan on katello_content_facets  (cost=0.00..8.90 rows=390 width=8) (actual time=0.006..0.106 rows=390 loops=1)
                     ->  Materialize  (cost=0.00..1092.04 rows=20574 width=4) (actual time=0.033..447.975 rows=3565031 loops=1)
                           ->  Index Only Scan using katello_content_facet_errata_eid_caid on katello_content_facet_errata  (cost=0.00..1040.61 rows=20574 width=4) (actual time=0.030..14.897 rows=12826 loops=1)
                                 Heap Fetches: 12456
 Total runtime: 1011615.776 ms
(30 rows)

Time: 1000108.780 ms
foreman=#