Query Optimization: EXPLAIN, Slow Logs & Avoiding N+1

Using EXPLAIN to Analyze Queries

SQL Server - Execution Plan

-- Enable execution plan
SET NOCOUNT ON;
SET STATISTICS IO ON;
SET STATISTICS TIME ON;

-- Analyze query performance (Following naming conventions)
SELECT
    o.[OrderId],
    o.[OrderNumber],
    o.[TotalAmount],
    o.[Status],
    o.[CreatedOn],
    c.[CustomerName]
FROM [dbo].[Orders] o WITH(NOLOCK)
INNER JOIN [dbo].[Customers] c WITH(NOLOCK)
    ON o.[CustomerId] = c.[CustomerId]
WHERE o.[Status] = N'pending'
ORDER BY o.[CreatedOn] DESC
OFFSET 0 ROWS FETCH NEXT 100 ROWS ONLY
OPTION (RECOMPILE);

-- View actual execution plan (Ctrl+M in SSMS)
-- Look for:
-- - Table Scan (BAD) vs Index Seek (GOOD)
-- - Key Lookups (can be improved with covering indexes)
-- - Estimated vs Actual row counts
-- - WITH(NOLOCK) prevents blocking

Key Metrics:

• •Table Scan: Full table scan (BAD for large tables)
• •Index Seek: Using index efficiently (GOOD)
• •Key Lookup: Additional lookup needed (can be optimized)
• •Logical reads: Number of pages read (lower is better)
• •OPTION (RECOMPILE): Forces query recompilation for better plans

PostgreSQL EXPLAIN

EXPLAIN ANALYZE
SELECT
    o.id,
    o.order_number,
    o.total_amount,
    o.status,
    o.created_at,
    c.name
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE o.status = 'pending'
ORDER BY o.created_at DESC
LIMIT 100;

-- Output shows:
-- Seq Scan on orders  (cost=0.00..23.50 rows=5 width=100)
-- Index Scan on customers_pkey  (cost=0.28..8.30 rows=1 width=50)

MySQL EXPLAIN

EXPLAIN SELECT
    id,
    order_number,
    customer_id,
    status,
    total_amount
FROM orders
WHERE customer_id = 123 AND status = 'shipped';

-- Look for:
-- type: ALL (bad), index (better), ref/eq_ref (best)
-- key: which index is used
-- rows: number of rows examined

Identifying Slow Queries

SQL Server - Query Store

SET NOCOUNT ON;

-- Enable Query Store
ALTER DATABASE [YourDatabase]
SET QUERY_STORE = ON (
    OPERATION_MODE = READ_WRITE,
    DATA_FLUSH_INTERVAL_SECONDS = 900,
    INTERVAL_LENGTH_MINUTES = 60,
    MAX_STORAGE_SIZE_MB = 1000
);

-- Find top 10 slowest queries
SELECT TOP 10
    q.[query_id] AS [QueryId],
    qt.[query_sql_text] AS [QueryText],
    rs.[count_executions] AS [ExecutionCount],
    rs.[avg_duration] / 1000.0 AS [AvgDurationMs],
    rs.[max_duration] / 1000.0 AS [MaxDurationMs],
    rs.[avg_logical_io_reads] AS [AvgLogicalIoReads],
    rs.[avg_cpu_time] / 1000.0 AS [AvgCpuMs]
FROM [sys].[query_store_query] q WITH(NOLOCK)
INNER JOIN [sys].[query_store_query_text] qt WITH(NOLOCK)
    ON q.[query_text_id] = qt.[query_text_id]
INNER JOIN [sys].[query_store_plan] p WITH(NOLOCK)
    ON q.[query_id] = p.[query_id]
INNER JOIN [sys].[query_store_runtime_stats] rs WITH(NOLOCK)
    ON p.[plan_id] = rs.[plan_id]
WHERE rs.[avg_duration] > 1000000  -- > 1 second (in microseconds)
ORDER BY rs.[avg_duration] DESC
OPTION (RECOMPILE);

-- Find queries with high logical reads (I/O intensive)
SELECT TOP 10
    qt.[query_sql_text] AS [QueryText],
    rs.[count_executions] AS [ExecutionCount],
    rs.[avg_logical_io_reads] AS [AvgLogicalIoReads],
    rs.[avg_duration] / 1000.0 AS [AvgDurationMs]
FROM [sys].[query_store_query] q WITH(NOLOCK)
INNER JOIN [sys].[query_store_query_text] qt WITH(NOLOCK)
    ON q.[query_text_id] = qt.[query_text_id]
INNER JOIN [sys].[query_store_plan] p WITH(NOLOCK)
    ON q.[query_id] = p.[query_id]
INNER JOIN [sys].[query_store_runtime_stats] rs WITH(NOLOCK)
    ON p.[plan_id] = rs.[plan_id]
ORDER BY rs.[avg_logical_io_reads] DESC
OPTION (RECOMPILE);

PostgreSQL - Enable Slow Query Log

-- In postgresql.conf
log_min_duration_statement = 1000  -- Log queries > 1 second

-- Or set per session
SET log_min_duration_statement = 1000;

-- View pg_stat_statements
SELECT
    query,
    calls,
    total_exec_time,
    mean_exec_time,
    max_exec_time
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

MySQL - Slow Query Log

-- Enable slow query log
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;  -- 1 second threshold
SET GLOBAL log_queries_not_using_indexes = 'ON';

-- Analyze with pt-query-digest
pt-query-digest /var/log/mysql/slow-query.log

The N+1 Query Problem

The Problem

// BAD: N+1 queries
const users = await db.query(
  'SELECT id, name, email FROM users ORDER BY id OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY'
);

for (const user of users) {
  // This runs 10 separate queries!
  const posts = await db.query(
    'SELECT id, title, content, created_at FROM posts WHERE user_id = @userId',
    { userId: user.id }
  );
  user.posts = posts;
}
// Result: 1 + 10 = 11 queries (Very slow!)

Solution 1: JOIN (T-SQL)

SET NOCOUNT ON;

-- GOOD: Single query with JOIN
SELECT
    u.[Id] AS [UserId],
    u.[Name] AS [UserName],
    u.[Email] AS [UserEmail],
    p.[Id] AS [PostId],
    p.[Title] AS [PostTitle],
    p.[Content] AS [PostContent],
    p.[CreatedAt] AS [PostCreatedAt]
FROM [dbo].[Users] u WITH(NOLOCK)
LEFT JOIN [dbo].[Posts] p WITH(NOLOCK)
    ON p.[UserId] = u.[Id]
WHERE u.[Id] IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10)
ORDER BY u.[Id], p.[CreatedAt] DESC
OPTION (RECOMPILE);
-- Result: 1 query (Much faster!)

Solution 2: Eager Loading (ORM)

// Sequelize (Node.js)
const users = await User.findAll({
  include: [{ model: Post }]  // Eager load posts
});

// Entity Framework (C#)
var users = await context.Users
  .Include(u => u.Posts)
  .ToListAsync();

// Result: 2 queries (users + all posts at once)

Solution 3: DataLoader (GraphQL)

const DataLoader = require('dataloader');

const postLoader = new DataLoader(async (userIds) => {
  // Single batched query with explicit columns
  const posts = await db.query(
    'SELECT id, user_id, title, content, created_at FROM posts WHERE user_id IN (@userIds)',
    { userIds }
  );

  // Group by user_id
  const grouped = userIds.map(id =>
    posts.filter(p => p.user_id === id)
  );

  return grouped;
});

// Usage - automatically batches requests
const posts = await postLoader.load(userId);

Query Optimization Checklist

1. •Add indexes on WHERE/JOIN columns (T-SQL)

SET NOCOUNT ON;

-- Create composite index for common queries
CREATE NONCLUSTERED INDEX [IX_Orders_CustomerId_Status]
ON [dbo].[Orders]([CustomerId], [Status])
INCLUDE ([OrderNumber], [TotalAmount], [CreatedAt]);

-- Check index usage
SELECT
    i.[name] AS [IndexName],
    s.[user_seeks] AS [UserSeeks],
    s.[user_scans] AS [UserScans],
    s.[user_lookups] AS [UserLookups],
    s.[user_updates] AS [UserUpdates]
FROM [sys].[dm_db_index_usage_stats] s WITH(NOLOCK)
INNER JOIN [sys].[indexes] i WITH(NOLOCK)
    ON s.[object_id] = i.[object_id]
    AND s.[index_id] = i.[index_id]
WHERE OBJECT_NAME(s.[object_id]) = N'Orders'
OPTION (RECOMPILE);

2. •Use TOP/OFFSET-FETCH to limit result sets

SET NOCOUNT ON;

-- T-SQL: Use TOP
SELECT TOP 100
    [Id],
    [OrderNumber],
    [CustomerId],
    [TotalAmount],
    [CreatedAt]
FROM [dbo].[Orders] WITH(NOLOCK)
ORDER BY [CreatedAt] DESC
OPTION (RECOMPILE);

-- T-SQL: Use OFFSET-FETCH (SQL Server 2012+)
SELECT
    [Id],
    [OrderNumber],
    [CustomerId],
    [TotalAmount],
    [CreatedAt]
FROM [dbo].[Orders] WITH(NOLOCK)
ORDER BY [CreatedAt] DESC
OFFSET 0 ROWS FETCH NEXT 100 ROWS ONLY
OPTION (RECOMPILE);

3. •**Always specify columns - NEVER use SELECT ***

SET NOCOUNT ON;

-- BAD: Fetches all columns (wasteful)
SELECT * FROM [dbo].[Users] WITH(NOLOCK);

-- GOOD: Only fetch needed columns
SELECT
    [Id],
    [Name],
    [Email],
    [CreatedAt]
FROM [dbo].[Users] WITH(NOLOCK)
OPTION (RECOMPILE);

-- GOOD: Even better with WHERE clause
SELECT
    [Id],
    [Name],
    [Email]
FROM [dbo].[Users] WITH(NOLOCK)
WHERE [IsActive] = 1
OPTION (RECOMPILE);

4. •Use EXISTS instead of COUNT for existence checks

SET NOCOUNT ON;

-- BAD: Counts all rows (slow)
DECLARE @customerid INT = 123;

IF (SELECT COUNT(*) FROM [dbo].[Orders] WITH(NOLOCK) WHERE [CustomerId] = @customerid) > 0
BEGIN
    PRINT N'Customer has orders';
END

-- GOOD: Stops at first match (fast)
IF EXISTS(SELECT 1 FROM [dbo].[Orders] WITH(NOLOCK) WHERE [CustomerId] = @customerid)
BEGIN
    PRINT N'Customer has orders';
END

5. •Batch operations instead of loops

SET NOCOUNT ON;

-- BAD: Multiple single INSERT statements
INSERT INTO [dbo].[Logs] ([Message], [CreatedAt]) VALUES (N'Log 1', GETDATE());
INSERT INTO [dbo].[Logs] ([Message], [CreatedAt]) VALUES (N'Log 2', GETDATE());
INSERT INTO [dbo].[Logs] ([Message], [CreatedAt]) VALUES (N'Log 3', GETDATE());

-- GOOD: Single batch insert
INSERT INTO [dbo].[Logs] ([Message], [CreatedAt])
VALUES
    (N'Log 1', GETDATE()),
    (N'Log 2', GETDATE()),
    (N'Log 3', GETDATE());

-- BEST: Use table-valued parameters for large batches
CREATE TYPE [dbo].[LogTableType] AS TABLE (
    [Message] NVARCHAR(MAX),
    [CreatedAt] DATETIME2
);
GO

-- In stored procedure
CREATE PROCEDURE [dbo].[InsertLogs_v1]
    @logs [dbo].[LogTableType] READONLY
AS
BEGIN
    SET NOCOUNT ON;

    INSERT INTO [dbo].[Logs] ([Message], [CreatedAt])
    SELECT [Message], [CreatedAt] FROM @logs;
END

Real-World Optimization Example

Before (Slow - 2.5 seconds)

SET NOCOUNT ON;

-- BAD: Subquery, no indexes, no date filter
SELECT
    o.[Id],
    o.[OrderNumber],
    o.[CustomerId],
    o.[TotalAmount],
    o.[Status],
    o.[CreatedAt]
FROM [dbo].[Orders] o WITH(NOLOCK)
WHERE o.[CustomerId] IN (
    SELECT c.[Id] FROM [dbo].[Customers] c WITH(NOLOCK) WHERE c.[Country] = N'US'
)
ORDER BY o.[TotalAmount] DESC;

-- Issues:
-- 1. Subquery is inefficient (should use INNER JOIN)
-- 2. No date filter (scanning years of data)
-- 3. Missing indexes
-- 4. No row limit (TOP or OFFSET-FETCH)
-- 5. No OPTION (RECOMPILE) for better query plans

After (Fast - 0.05 seconds, 50x faster!)

SET NOCOUNT ON;

-- GOOD: Explicit columns, INNER JOIN, indexes, date filter, TOP, OPTION (RECOMPILE)
SELECT TOP 1000
    o.[Id],
    o.[OrderNumber],
    o.[TotalAmount],
    o.[CreatedAt],
    c.[Name] AS [CustomerName]
FROM [dbo].[Orders] o WITH(NOLOCK)
INNER JOIN [dbo].[Customers] c WITH(NOLOCK)
    ON o.[CustomerId] = c.[Id]
WHERE c.[Country] = N'US'
    AND o.[CreatedAt] >= DATEADD(YEAR, -1, GETDATE())
ORDER BY o.[TotalAmount] DESC
OPTION (RECOMPILE);

-- Added covering indexes:
CREATE NONCLUSTERED INDEX [IX_Customers_Country]
ON [dbo].[Customers]([Country])
INCLUDE ([Id], [Name]);

CREATE NONCLUSTERED INDEX [IX_Orders_CustomerId_CreatedAt]
ON [dbo].[Orders]([CustomerId], [CreatedAt])
INCLUDE ([Id], [OrderNumber], [TotalAmount]);

-- Verify index usage
SET STATISTICS IO ON;
-- Run query again and check logical reads

Performance Comparison

SET NOCOUNT ON;

-- Check execution stats
SELECT TOP 10
    qs.[execution_count] AS [ExecutionCount],
    qs.[total_elapsed_time] / 1000000.0 AS [TotalElapsedTimeSec],
    qs.[total_elapsed_time] / qs.[execution_count] / 1000.0 AS [AvgElapsedTimeMs],
    SUBSTRING(qt.[text], (qs.[statement_start_offset]/2)+1,
        ((CASE qs.[statement_end_offset]
            WHEN -1 THEN DATALENGTH(qt.[text])
            ELSE qs.[statement_end_offset]
        END - qs.[statement_start_offset])/2) + 1) AS [QueryText]
FROM [sys].[dm_exec_query_stats] qs WITH(NOLOCK)
CROSS APPLY [sys].[dm_exec_sql_text](qs.[sql_handle]) qt
WHERE qt.[text] LIKE N'%Orders%Customers%'
ORDER BY qs.[total_elapsed_time] DESC
OPTION (RECOMPILE);

Monitoring Tools

SQL Server

• •Query Store: Built-in query performance tracking
• •DMVs: sys.dm_exec_query_stats, sys.dm_db_index_usage_stats
• •SQL Server Profiler: Real-time query monitoring
• •Extended Events: Lightweight performance monitoring
• •sp_BlitzCache: Brent Ozar's free query analysis tool

PostgreSQL

• •pg_stat_statements: Query statistics
• •pgBadger: Log analyzer
• •EXPLAIN ANALYZE: Query execution plans

MySQL

• •MySQL Workbench: Visual query profiling
• •Percona Toolkit: pt-query-digest
• •slow_query_log: Built-in slow query logging

Application Level

• •New Relic APM: Database query monitoring
• •Datadog: Database performance dashboards
• •Application Insights: Azure SQL monitoring
• •Custom: Log slow queries and analyze regularly