Project

General

Profile

Bug #25884

slow errata query on dashboard

Added by Partha Aji over 3 years ago. Updated over 3 years ago.

Status:
Closed
Priority:
Normal
Assignee:
Category:
Dashboard
Target version:
Difficulty:
Triaged:
Yes
Bugzilla link:
Fixed in Releases:
Found in Releases:
Red Hat JIRA:

Description

Steps to Reproduce:
The errata query running while loading the `Latest Errata` widget is slow and it takes a minute or so to complete in an environment with tens of thousands of content hosts.

Actual results:
From a customer postgres log:
2018-11-13 15:48:49 CET LOG: duration: 57899.066 ms execute <unnamed>: 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_facets" ON "katello_content_facets"."id" = "katello_content_facet_errata"."content_facet_id" WHERE "katello_content_facets"."host_id" IN (62722, 62491, 62714, 55649, 76365, 71628, 47225, 60610, 65394, 65702, 76176, 76414, 62661, 76168, 69493, 5852, 75420, 56275, 69760, 5851, 5951, 62715, 71077, 1900, 1898, 69768, 68857, 62809, 68860, 65819, 7941, 46094, 68799, 296, 3918, 7920, 68859, 53753, 17840, 69645, 71079, 20953, 49090, 64997, 53764, 21250, 69437, 71047, 46977, 3, 65822, 65821, 47224, 65820, 298, 65703, 69657, 68858, 10046, 5636, 76170, 16608, 45148, 5631, 22449, 65823, 2866, 2274, 5630, 5628, 3427, 5560, 18091, 47064, 5612, 6988, 6760, 69769, 69354, 48893, 39879, 46986, 7115, 5627, 34668, 15825, 17334, 20733, 52097, 54362, 65563, 65399, 45808, 49326, 299, 49325, 62483, 1902, 71078, 50379, 34996, 71076, 40335, 69762, 73328, 76220, 69432, 1905, 46083, 73801, 64679, 47065, 69761, 62670, 44980, 45149, 72054, 66401, 19193, 1904, 60612, 44373, 62669, 62664, 69346, 482, 46082, 72483, 45529, 62671, 63140, 48894, 38722, 53496, 45706, 72639, 45653, 62668, 66661, 41251, 41482, 65558, 34321, 34322, 163, 57429, 67659, 39796, 72405, 72329, 34995, 4966, 44471, 42644, 66615, 72365, 52555, 58735, 62437, 57940, 69774, 45866, 41483, 71254, 280, 71482, 72494, 69348, 43954, 25170, 72607, 73764, 1895, 56330, 67382, 57156, 46095, 57155, 71606, 18650, 45840, 63979, 45865, 71452, 66390, 64047, 25174, 57197, 57428, 69347, 66766, 20434, 75419, 56619, 62654, 72627, 69072, 60122, 60633, 62485, 34665, 62488, 45810, 62720, 64682, 74941, 66758, 58631, 69773, 56190, 43256, 25173, 45523, 45248, 57112, 39836, 62487, 62718, 45235, 1864, 45237, 57304, 62666, 45239, 57941, 57430, 48763, 62482, 25172, 22793, 57244, 57221, 46331, 45124, 45123, 53255, 25171, 53523, 22699, 45236, 52553, 62644, 49182, 48594, 56617, 56288, 32317, 56132, 43955, 1901, 62568, 53500, 45841, 50157, 56329, 56332, 966, 20433, 21225, 4002)) ORDER BY katello_errata.updated desc LIMIT 6

Expected results:
the query should be optimized to run faster.

Associated revisions

Revision 227f1ab0 (diff)
Added by Partha Aji over 3 years ago

Fixes #25884 - Speeds up Dashboard errata query (#7931)

This commit speeds up the dashboard errata query to handle 1000s of host
ids. Prior to this commit the sql run would look like
SELECT "katello_errata".* .... where ....
katello_content_facets"."host_id" IN (1000s of host ids)
to

SELECT "katello_errata".* .... where ....
katello_content_facets"."host_id" IN (<select id from authorized hosts>)

This results in a 1000x speedup

History

#1 Updated by Partha Aji over 3 years ago

  • Bugzilla link set to 1650259

#2 Updated by The Foreman Bot over 3 years ago

  • Status changed from New to Ready For Testing
  • Pull request https://github.com/Katello/katello/pull/7931 added

#3 Updated by Anonymous over 3 years ago

  • Status changed from Ready For Testing to Closed

#4 Updated by John Mitsch over 3 years ago

  • Triaged changed from No to Yes
  • Target version set to Katello 3.12.0

Also available in: Atom PDF