simulate-workload.sql

-- Simulate realistic workload by running queries multiple times
-- Run this after setup-test-db.sql to populate pg_stat_statements

\echo 'Simulating realistic query workload...'
\echo 'This will take ~30 seconds...'
\echo ''

-- Reset stats first
SELECT pg_stat_statements_reset();

-- User login (very frequent - parameterized query will be tracked with execution count)
\echo '1. Simulating user login queries (100x)...'
\set i 1
\set end 100
SELECT * FROM users WHERE email = 'user' || generate_series(1, 100) || '@example.com' \gexec

-- Active user searches
\echo '2. Simulating active user searches (50x)...'
SELECT FORMAT('SELECT * FROM users WHERE status = ''active'' AND country = ''US'' LIMIT 100;') FROM generate_series(1, 50) \gexec

-- User order history (very frequent - dashboard)
\echo '3. Simulating user order history queries (100x)...'
SELECT FORMAT('SELECT * FROM orders WHERE user_id = %s ORDER BY created_at DESC LIMIT 10;', floor(random() * 50000)::int + 1) FROM generate_series(1, 100) \gexec

-- Order details with items
\echo '4. Simulating order detail queries (80x)...'
SELECT FORMAT('SELECT o.*, oi.*, p.name, p.price FROM orders o JOIN order_items oi ON oi.order_id = o.id JOIN products p ON p.id = oi.product_id WHERE o.id = %s;', floor(random() * 200000)::int + 1) FROM generate_series(1, 80) \gexec

-- Product listings by category (very frequent)
\echo '5. Simulating product listing queries (100x)...'
SELECT FORMAT('SELECT * FROM products WHERE category_id = %s AND status = ''active'' ORDER BY created_at DESC LIMIT 20;', floor(random() * 11)::int + 1) FROM generate_series(1, 100) \gexec

-- Product detail page
\echo '6. Simulating product detail page queries (60x)...'
SELECT FORMAT('SELECT * FROM products WHERE slug = ''product-%s'';', floor(random() * 10000)::int) FROM generate_series(1, 60) \gexec

-- Pending orders (admin dashboard)
\echo '7. Simulating admin dashboard queries (40x)...'
SELECT FORMAT('SELECT * FROM orders WHERE status = ''pending'' ORDER BY created_at DESC LIMIT 50;') FROM generate_series(1, 40) \gexec

-- User sessions
\echo '8. Simulating user session queries (40x)...'
SELECT FORMAT('SELECT * FROM sessions WHERE user_id = %s ORDER BY started_at DESC LIMIT 20;', floor(random() * 50000)::int + 1) FROM generate_series(1, 40) \gexec

-- Top products (complex aggregation)
\echo '9. Simulating analytics queries (20x)...'
SELECT FORMAT('SELECT p.id, p.name, SUM(oi.quantity) as total_sold FROM products p JOIN order_items oi ON oi.product_id = p.id JOIN orders o ON o.id = oi.order_id WHERE o.created_at > NOW() - INTERVAL ''30 days'' GROUP BY p.id, p.name ORDER BY total_sold DESC LIMIT 10;') FROM generate_series(1, 20) \gexec

-- User order summary
\echo '10. Simulating user summary queries (50x)...'
SELECT FORMAT('SELECT u.id, u.email, COUNT(o.id) as order_count, SUM(o.total) as total_spent FROM users u LEFT JOIN orders o ON o.user_id = u.id WHERE u.id = %s GROUP BY u.id, u.email;', floor(random() * 50000)::int + 1) FROM generate_series(1, 50) \gexec

\echo ''
\echo 'Workload simulation complete!'
\echo ''
\echo 'Query statistics:'
SELECT
  COUNT(*) as unique_query_patterns,
  SUM(calls) as total_executions,
  ROUND(AVG(mean_exec_time)::numeric, 2) as avg_time_ms
FROM pg_stat_statements
WHERE query NOT LIKE '%pg_stat_statements%'
  AND query NOT LIKE '%SHOW%'
  AND query NOT LIKE '%CREATE EXTENSION%'
  AND query NOT LIKE '%FORMAT%'
  AND query NOT LIKE '%generate_series%';

\echo ''
\echo 'Top queries by execution count:'
SELECT
  LEFT(query, 80) as query_preview,
  calls,
  ROUND(mean_exec_time::numeric, 2) as avg_ms
FROM pg_stat_statements
WHERE query NOT LIKE '%pg_stat_statements%'
  AND query NOT LIKE '%SHOW%'
  AND query NOT LIKE '%CREATE EXTENSION%'
  AND query NOT LIKE '%FORMAT%'
  AND query NOT LIKE '%generate_series%'
ORDER BY calls DESC
LIMIT 10;