The variable list_of_package_ids - where does it come from?
- Is it some user-dependent input?
- Is it needed in every portlet content query? Or only once per page?
On a side note: is null clauses usually lead to indexes not being used because null values are not stored in a b*tree index. That might be another performance-detrimental query clause.
I think you should take out certain parts of a particularly expensive query and play around a fair bit to get a feeling what exactly causes sub-optimal performance.
As you have production-size data, your results would really matter. I am tinkering with an empty system 🤔
