**Complete Optimization Workflow:**

-- Step 1: Identify slow queries

SELECT

query,

calls,

total_exec_time / 1000 as total_seconds,

mean_exec_time / 1000 as avg_seconds

FROM pg_stat_statements

ORDER BY total_exec_time DESC

LIMIT 10;

-- Step 2: Analyze specific query

EXPLAIN ANALYZE

SELECT * FROM orders WHERE customer_id = 123;

-- Step 3: Check for indexes

SELECT

schemaname,

tablename,

indexname,

indexdef

FROM pg_indexes

WHERE tablename = 'orders';

-- Step 4: Check statistics age

SELECT

schemaname,

tablename,

last_analyze,

n_live_tup,

n_mod_since_analyze

FROM pg_stat_user_tables

WHERE tablename = 'orders';

-- Step 5: Apply fixes

-- Add missing index

CREATE INDEX idx_orders_customer_id ON orders(customer_id);

-- Update statistics

ANALYZE orders;

-- Increase work_mem for specific query

SET work_mem = '256MB';

-- Step 6: Verify improvement

EXPLAIN ANALYZE

SELECT * FROM orders WHERE customer_id = 123;