Detect N+1 database query on transactions

Hello,

With new-relic transaction traces, I detected a N+1 query (n plus one query) issue in one of my API endpoint. Meaning that for every row return by a SQL query, I do another query per row to enrich the data.
More reading on this problem here https://medium.com/doctolib/understanding-and-fixing-n-1-query-30623109fe89

To detect this issue, I looked at the count of database call on a particular query. In the picture, we can see 25 calls to Postgres job_application.select, which is not normal in this case.

I was wondering wether it was possible or not to detect this automatically with a query.
I played a bit with query builder but got nothing really useful.

What I tried so far:

  • Look for transaction with many database count, but this is not the only sign of N+1, indeed, we’re looking at high count of database call on the same query, which is not the case here
SELECT databaseCallCount, databaseDuration, appName, name FROM Transaction WHERE databaseCallCount > 25 ORDER BY databaseCallCount DESC
  • I also try to look at metrics with filter onmetricTimesliceName but I didn’t understood exactly how it worked

I know some APM like appSignal have this feature, and I’m quite sure I can find a way with new-relic, but I’m stuck for now

Hi, @antoinej: Try grouping by (FACET) appName and transaction name:

SELECT average(databaseCallCount) FROM Transaction FACET appName, name
1 Like