Description of problem:
User story: a Satellite with 275k packages is imperformant when querying all packages.

The reason is how scoped_search method works - basically it attempt to hunt too many tails. In our case "get me all packages", it raises at least two complete/complex search queries "get me all packages associated to repos in (list of all repos)".

The nested "associated to repos in <all_repos>" is one performance penalty that is worth of exploring possible simplification (and I haven't dug into it at all).

The second / main point is the repetitive search queries that are sometimes redundant but invoked due to the "let hunt all the tails" reason. I.e.:

    def scoped_search(query, default_sort_by, default_sort_order, options = {})
..
      total = scoped_search_total(query, group)
..
(some optional changes to the query)
..
      subtotal = total.zero? ? 0 : scoped_search_total(query, group)

If the query is not changed at all, we call fully redundant scoped_search_total (which might but not need to be cached).

If the query is changed just cosmetically (well, how to detect it?), we run full scoped_search_total as well. Despite we are not interested in subtotals at all.

Similarly, when we are interested in subtotals, we still invoke a *full* query (*not* only "SELECT COUNT(*)", but full "SELECT *") to feed "total" variable.

TL;DR: the scoped_search should have some smart conditions "invoke this search only when really demanded". This can save us half of search time.



Version-Release number of selected component (if applicable):
Sat 6.11.*


How reproducible:
100%


Steps to Reproduce:
0. Optionally enable sql debugs to see particular sql queries raised by katello - see https://access.redhat.com/solutions/2252291 how to enable it.
1. Have a Satellite with >200k packages; optionally play with :packages_restrict_latest settings (default=false is more evident for the bugzilla).
2. Add to /usr/share/gems/gems/katello-*/app/controllers/katello/api/v2/api_controller.rb debugs per Additional info.
3. Invoke a "get me all packages" query:

time curl -u admin:PASSWORD -k "https://$(hostname -f)/katello/api/v2/packages?organization_id=1" > /dev/null 2>&1

4. Check in production.log timestamps of the debugs (and overall execution time of the query/request.


Actual results:
4. Two similarly long delays between debugs appear, like:

2022-11-08T12:12:07 [I|app|6547c669] 03347790: index_relation to return, size=275444
2022-11-08T12:12:24 [I|app|6547c669] 03347790: final_custom_index_relation collection=275444

and:

2022-11-08T12:12:24 [I|app|6547c669] 03347790: scoped_search, group=
2022-11-08T12:12:37 [I|app|6547c669] 03347790: scoped_search, subtotal=275444

pointing to these methods taking too long (and one is redundantly called for our use case):

      total = scoped_search_total(query, group)
..
(some optional changes to the query)
..
      subtotal = total.zero? ? 0 : scoped_search_total(query, group)

And overall execution time spans many tens of seconds.


Expected results:
For such plain requests, katello should not query total or subtotal (or, if necessary, query their counts only - verify with sql debugs).

For overall execution time: it is questionable how high times are still acceptable, but for even a scaled Satellite, opening WebUI Packages page should not last >30 seconds, imho.


Additional info:
To make this even more complex, the query *object* is changing despite it shouldnt, by adding debugs:

      Rails.logger.info("03347790: total query=#{query} group=#{group}")
      total = scoped_search_total(query, group)
      # If you get an ArgumentError here (wrong number of arguments given)
      # it's probably because query is an array and not an ActiveRecord::Relation
      query = query.select(:id) if query.respond_to?(:select)
      Rails.logger.info("03347790: select query=#{query} group=#{group}")
      query = resource.search_for(*search_options).where("#{resource.table_name}.id" => query)
      Rails.logger.info("03347790: search_for query=#{query} group=#{group}")

      query = self.final_custom_index_relation(query) if self.respond_to?(:final_custom_index_relation)

      Rails.logger.info("03347790: final_custom_index_relation query=#{query} group=#{group}")
      query = query.select(group).group(group) if group
      Rails.logger.info("03347790: second total, query=#{query} group=#{group}")
      subtotal = total.zero? ? 0 : scoped_search_total(query, group)
      Rails.logger.info("03347790: scoped_search, subtotal=#{subtotal}")
      selectable = total.zero? ? 0 : scoped_search_total_selectable(query, group)

      Rails.logger.info("03347790: scoped_search, custom_sort")

then I got:

2022-11-13T15:21:36 [I|app|4e42087b] 03347790: total query=#<Katello::Rpm::ActiveRecord_Relation:0x00005605f8ff16b8> group=
2022-11-13T15:21:39 [I|app|4e42087b] 03347790: select query=#<Katello::Rpm::ActiveRecord_Relation:0x00007f1b5920ed08> group=
2022-11-13T15:21:39 [I|app|4e42087b] 03347790: search_for query=#<Katello::Rpm::ActiveRecord_Relation:0x00007f1b5920d930> group=
2022-11-13T15:21:39 [I|app|4e42087b] 03347790: final_custom_index_relation start, BT=/usr/share/gems/gems/katello-4.3.0.47/app/controllers/katello/api/v2/api_controller.rb:97:in `scoped_search'
2022-11-13T15:21:39 [I|app|4e42087b] 03347790: final_custom_index_relation restrict_latest=false
2022-11-13T15:21:39 [I|app|4e42087b] 03347790: final_custom_index_relation collection=39579
2022-11-13T15:21:39 [I|app|4e42087b] 03347790: final_custom_index_relation query=#<Katello::Rpm::ActiveRecord_Relation:0x00007f1b5920d930> group=
2022-11-13T15:21:39 [I|app|4e42087b] 03347790: second total, query=#<Katello::Rpm::ActiveRecord_Relation:0x00007f1b5920d930> group=

but why query object changed e.g. by:

query = self.final_custom_index_relation(query) if self.respond_to?(:final_custom_index_relation)

if Katello::Api::V2::PackagesController.final_custom_index_relation performs *nothing* when we have params[:packages_restrict_latest] = false ?)