Skip to main content
2026-07-29 16:01
Database

Query Performance Analysis

QueryValidateExpert
Try Interactive Demo

What you will learn

  • EXPLAIN (without ANALYZE) shows the PLAN; EXPLAIN ANALYZE RUNS the query + measures. Use ANALYZE only on test data
  • Look for 'Seq Scan' on large tables — usually means missing index. 'Index Scan' with low selectivity can also be slow
  • Compare planned rows vs actual rows — if planner is off by 10×, stats are stale. Run ANALYZE to refresh

Description

Use EXPLAIN to understand query execution plans.

Test Scenario

-- Analyze query execution

EXPLAIN QUERY PLAN
SELECT p.name, SUM(oi.quantity) as total_sold
FROM products p
JOIN order_items oi ON p.id = oi.product_id
GROUP BY p.id;

-- Look for:
-- SCAN = full table scan (slow)
-- SEARCH = index lookup (fast)
-- USING INDEX = index being used

-- Create index to improve:
CREATE INDEX idx_order_items_product ON order_items(product_id);

Testing Tips

  • •EXPLAIN shows query plan
  • •Look for SCAN vs SEARCH
  • •Indexes improve SEARCH operations
// Playwright API Testing - Database
import { test, expect } from '@playwright/test';


test('Database query via API', async ({ request }) => {
  const response = await request.post('/api/database/query', {
    data: {
      sql: 'SELECT * FROM users WHERE id = ?',
      params: [1]
    }
  });


  expect(response.ok()).toBeTruthy();
  const { rows } = await response.json();
  expect(rows.length).toBeGreaterThan(0);
});

Challenge ID: sql-explain-analyze

2 frameworks available