Investigate postgres permission checks performance for recent items in command palette
From @DylanGriffith's comment:
We probably should invest more time into this. All our searches (autocomplete, Elasticsearch, Postgres) has a post-filter which redacts data that the user may not have access to. Sometimes this redaction logic is inefficient and implements extra Postgres queries that could be preloaded. Even searches served by Elasticsearch do need to double check the permissions in Postgres before serving results to users to avoid leaking private data (which happened many times before).
My hypothesis and what I'm seeing from the logs is that our Postgres permission checks (or some other Postgres queries not related specifically to the search) are excessive:
Note that
generic
is autocomplete query param that corresponds to the recent results.You can see that the Redis component of this is usually very fast relative to the total duration. Since some of these responses have 70 Postgres queries happening I assume there is some N+1 query problems here. There is also a few hundred milliseconds of unaccounted for time in those metrics that we might need to dig into. Also worth noting with my latency from GitLab.com I tend to see a few hundred more
ms
than the duration shown there.But I believe this data shows me that there is almost no way we'd be able to get the results from Elasticsearch faster as it would still require the same Postgres queries even if the searching part is happening in Elasticsearch and the Redis search results are often less than
10ms
and we rarely see even the fastest Elasticsearch responses taking less than140ms
. Also those redis timings are including redis calls for data other than the search data (eg. user session and various other caching things).But I do think we should spend some backend time optimizing these things periodically because N+1 queries get added to the permission checks and other parts of our request stack over time and things get slower and slower.