Actions
Bug #14891
open
Slow query causing dashboard page to load very slowly
Description
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=#
Updated by Dominic Cleal about 8 years ago
- Project changed from Foreman to Katello
Updated by Eric Helms about 8 years ago
- translation missing: en.field_release set to 114
Actions