@gvde thanks for the info, catching up from a vacation.
It looks like we have the info that is needed, the increase in performance from the index on katello_rpms ( name, arch, evr ); is promising. We just need to trace the migration logic to double check the upgrade vs fresh installation scenarios.
Thanks. I am just wondering. Is there a file from which the schema is loaded during a fresh installation? I wanted to compare a database schema dump from my database with a fresh one to see if anything else is different. But I am somewhat lost in the foreman-installer puppet logic…
Even for a fresh installation, all of the migrations are run to prep the database. So, if there’s a difference in logic between upgrade vs fresh install, it must be related more to the state of the machine when it ran the migrations.
O.K. Was there a difference between 4.16.0 and 4.16.1? I have upgraded from 4.15.1 to 4.16.1 (skipping the .0). Is that maybe the reason why @Bernhard_Suttner has the index while I don’t? I guess he should see something in the foreman-installer logs what happened… I am really curios…
Aye, we should check this in case a change to the migration was backported. CC @qjames who is collaborating on this issue as well.
Please follow 38576 for the fix.
Thanks. I am somewhat confused from this thread. Does table katello_installed_packages also need an index? It is using evr as well.
I didn’t add the katello_installed_packages index because it was only the index on katello_rpms ( name, arch, evr ); that was lost, which caused the regression. My goal was to get the DB back in the sane state it was in before the EVR migration.
To be safe, I did some testing on a loaded DB and didn’t see much of a difference with the katello_installed_packages index, but it would be worth checking later if it would be helpful to have.
A query I tried took 11 seconds with no indexes, 0.2 seconds after adding the katello_rpms index, and 0.19 seconds after adding the new katello_installed_packages index.
Thank you. Is it necessary to wait till 4.17.1 for the fix or one can apply the fix manually ?
Feel free to add the new migration in, it should be safe to run multiple times without causing problems.
That means putting those two files to /usr/share/gems/gems/katello-4.16.2/db/migrate/ directory and running forman-installer ?
O.K. Good to know. Your second post in this thread lead me into the wrong direction. I suppose you didn’t want to compare indexes on katello_installed_packages with katello_rpms after all.
Exactly, yes. The Foreman Installer will then run foreman-rake db:migrate which will pick up the new migration
All of the good data in this thread led me to realize I was going down the wrong route with the katello_installed_packages index, so it was still very helpful 
I thought the installed packages index was missing originally because I did a comparison between the katello_rpms and the katello_installed_packages table, but that was mostly a starting guess.
Once it was discovered that the katello_rpms index was actually removed and not re-added, then it became more clear that we had a true regression.
I’m very happy about the collaboration here, this was a bad issue that we’ll need to figure out creating more regression testing for.
I applied the fix and the index has been recreated. I ran on one host a dnf uploadprofile --force-upload command and it triggered a foreman task which took 0s. There are no huge SELECT reported in postgresql log. So far it seems to be solved. Thank you all very much for a great collaboration.
Excellent to hear, let us know if the applicability tasks start piling up again.
I have placed the new migration into the directory and ran foreman-rake db:migrate. It created the index again.
I have checked the tasks list and all applicability tasks finished within 1-2s per host since then. I actually have trouble capturing one of the tasks in the list while active… 
So this looks all good here. Thanks.