Improve EnvironmentNamesFinder. namespace_environments database query performance
Background
This issue is opened as confidential since it's discovered during database review of a security MR and the security fix is not merged yet. After the security MR is merged, this issue can be converted to nonconfidential.
While working on a security MR, we have noticed a performance issue in the EnvironmentNamesFinder.namespace_environments.
Buffer comparison before and after security fix
| type | old query | new query |
|---|---|---|
| buffer hit | ~227.30 MiB | ~268.60 MiB |
| buffer read | ~348.30 MiB | ~385.00 MiB |
| buffer dirtied | ~8.60 MiB | ~9.50 MiB |
| buffer writes | 0 | 0\ |
| query plan | link | link |
After the security issue is fixed, performance issue should be tackled as part of this issue.
Query plan gave the following warnings
❗️ Query processes too much data to return a relatively small number of rows. – Reduce data cardinality as early as possible during the execution, using one or several of the following techniques: new indexes, partitioning, query rewriting, denormalization. See the visualization of the plan to understand which plan nodes are the main bottlenecks. <http://momjian.us/main/writings/pgsql/hw_performance/|Show details>
❗️ Add LIMIT – The number of rows in the result set is too big. Limit number of rows. <https://postgres.ai/#tip-add-limit|Show details>Proposal
- Wait for the security MR to be merged.
- Optimize the query considering the warnings mentioned above.