Plugins » Katello

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

Description of problem:

For this query, it's slow as molasses (34 seconds) mostly (I suspect) due to the on disk sort:

Sort Method: external merge Disk: 4690032kB

So, it’s a query that needs optimising - I don't think there is anything we can do with indexes , there are a few full table scans which may need some adding but I don't think it will offer much when you're hitting disk for the sort. From my basic sql it looks like we're sorting 4 million+ rows to pull the top 6 off!

foreman=# explain analyse SELECT DISTINCT ON (katello_errata.updated, katello_errata.id) 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_facets" ON "katello_content_facets"."id" = "katello_content_facet_errata"."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 (1))) ORDER BY katello_errata.updated desc LIMIT 6
;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=4913625.77..4913637.33 rows=6 width=723) (actual time=33464.480..33464.492 rows=6 loops=1)
-> Unique (cost=4913625.77..4945949.90 rows=16779 width=723) (actual time=33464.479..33464.490 rows=6 loops=1)
-> Sort (cost=4913625.77..4924400.48 rows=4309884 width=723) (actual time=33464.475..33464.477 rows=12 loops=1)
Sort Key: katello_errata.updated, katello_errata.id
Sort Method: external merge Disk: 4690032kB
-> Hash Join (cost=11788.57..284536.41 rows=4309884 width=723) (actual time=46.294..5187.307 rows=4317717 loops=1)
Hash Cond: (katello_content_facet_errata.erratum_id = katello_errata.id)
-> Hash Join (cost=8184.04..170295.91 rows=4309884 width=4) (actual time=28.080..1964.186 rows=4317717 loops=1)
Hash Cond: (katello_content_facet_errata.content_facet_id = katello_content_facets.id)
-> Seq Scan on katello_content_facet_errata (cost=0.00..70526.84 rows=4309884 width=8) (actual time=0.003..398.488 rows=4317717 loops=1)
-> Hash (cost=8006.55..8006.55 rows=14199 width=4) (actual time=28.051..28.051 rows=14138 loops=1)
Buckets: 2048 Batches: 1 Memory Usage: 498kB
-> Hash Join (cost=497.48..8006.55 rows=14199 width=4) (actual time=4.733..25.452 rows=14138 loops=1)
Hash Cond: (hosts.id = katello_content_facets.host_id)
-> Seq Scan on hosts (cost=0.00..7044.58 rows=28699 width=4) (actual time=0.011..14.037 rows=16933 loops=1)
Filter: (((type)::text = 'Host::Managed'::text) AND (organization_id = 1))
Rows Removed by Filter: 43
-> Hash (cost=319.99..319.99 rows=14199 width=8) (actual time=4.707..4.707 rows=14138 loops=1)
Buckets: 2048 Batches: 1 Memory Usage: 553kB
-> Seq Scan on katello_content_facets (cost=0.00..319.99 rows=14199 width=8) (actual time=0.007..2.275 rows=14138 loops=1)
-> Hash (cost=1853.79..1853.79 rows=16779 width=723) (actual time=18.191..18.191 rows=16779 loops=1)
Buckets: 1024 Batches: 4 Memory Usage: 3213kB
-> Seq Scan on katello_errata (cost=0.00..1853.79 rows=16779 width=723) (actual time=0.002..2.842 rows=16779 loops=1)
Total runtime: 34360.051 ms
(24 rows)