Spotting the N+1 pattern in pg_stat_statements
Queries with negligible per-call time but called hundreds of thousands of times betray an ORM stuck in an N+1 loop: individually invisible, collectively dominant.
Prerequisites
PostgreSQL + extension pg_stat_statements
SQL
SELECT
LEFT(regexp_replace(query, '\s+', ' ', 'g'), 58) AS requete,
calls,
ROUND(mean_exec_time::numeric, 3) AS ms_unitaire,
ROUND(total_exec_time::numeric / 1000, 1) AS total_s,
ROUND(100.0 * total_exec_time
/ SUM(total_exec_time) OVER (), 1) AS part_pct
FROM pg_stat_statements
WHERE calls > 100000
AND mean_exec_time < 1.0
ORDER BY calls DESC
LIMIT 4;Result
requete | calls | ms_unitaire | total_s | part_pct ------------------------------------------------------------+----------+-------------+---------+---------- SELECT * FROM products WHERE product_id = $1 | 18412044 | 0.082 | 1509.8 | 31.4 SELECT * FROM customers WHERE customer_id = $1 | 8841204 | 0.094 | 831.1 | 17.3 SELECT price FROM price_list WHERE product_id = $1 AND ... | 4120448 | 0.121 | 498.6 | 10.4 SELECT COUNT(*) FROM stock WHERE product_id = $1 | 2204811 | 0.088 | 194.0 | 4.0 (4 rows) 63 % du temps base = 4 requêtes unitaires en boucle — un JOIN ou un cache les élimine
SQLPerformanceN+1pg_stat_statements