Query plan caching and prepared statements

-- Shared plan cache (across connections)
-- Only for identical queries

SELECT * FROM users WHERE id = 123;
SELECT * FROM users WHERE id = 456;
-- Different parameters -> different plans initially

-- With prepared statement:
PREPARE get_user (INT) AS SELECT * FROM users WHERE id = $1;
EXECUTE get_user(123);
EXECUTE get_user(456);
-- Same plan reused

-- Plan invalidation
-- Plans invalidated when:
-- 1. Table statistics change significantly
ANALYZE users;
-- All plans involving 'users' may be invalidated

-- 2. Schema changes
ALTER TABLE users ADD COLUMN last_login TIMESTAMP;
-- Plans invalidated

-- 3. Index created/dropped
CREATE INDEX idx_users_email ON users(email);
-- Plans invalidated

-- 4. Configuration changes
SET work_mem = '100MB';
-- May affect plans

-- Monitoring plan cache effectiveness
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Top queries by execution time
SELECT
  calls,
  mean_exec_time,
  total_exec_time,
  query
FROM pg_stat_statements
WHERE query NOT LIKE '%pg_stat_statements%'
ORDER BY total_exec_time DESC
LIMIT 20;

-- Check plan cache hit ratio
SELECT
  query,
  calls,
  plans,
  total_plan_time,
  total_exec_time,
  total_plan_time / NULLIF(plans, 0) AS avg_plan_time,
  total_exec_time / NULLIF(calls, 0) AS avg_exec_time
FROM pg_stat_statements
WHERE plans > 0
ORDER BY total_plan_time DESC
LIMIT 20;

-- Reset statistics
SELECT pg_stat_statements_reset();

-- Best practices for prepared statements

-- 1. Use for repeated queries
-- Good:
PREPARE frequent_query (INT) AS
SELECT * FROM products WHERE category_id = $1;

-- Execute many times
EXECUTE frequent_query(1);
EXECUTE frequent_query(2);

-- 2. Clean up unused statements
DEALLOCATE frequent_query;

-- Or deallocate all
DEALLOCATE ALL;

-- 3. Be careful with data distribution
-- If some parameter values have very different selectivity:

CREATE TABLE orders_skewed (
  id SERIAL PRIMARY KEY,
  user_id INT,
  status VARCHAR(20)
);

-- 99% of orders have status='completed', 1% have status='pending'
INSERT INTO orders_skewed (user_id, status)
SELECT
  (random() * 1000)::INT,
  CASE WHEN random() < 0.99 THEN 'completed' ELSE 'pending' END
FROM generate_series(1, 100000);

CREATE INDEX idx_orders_skewed_status ON orders_skewed(status);
ANALYZE orders_skewed;

-- Custom plan for specific value
EXPLAIN ANALYZE
SELECT * FROM orders_skewed WHERE status = 'pending';
-- Uses index (selective)

EXPLAIN ANALYZE
SELECT * FROM orders_skewed WHERE status = 'completed';
-- May use seq scan (not selective)

-- Generic plan compromises
PREPARE get_orders_by_status (VARCHAR) AS
SELECT * FROM orders_skewed WHERE status = $1;

-- May choose suboptimal plan
EXECUTE get_orders_by_status('pending');
EXECUTE get_orders_by_status('completed');

-- Solution: Force custom plans
SET plan_cache_mode = 'force_custom_plan';

-- Or: Separate queries for different cases
PREPARE get_pending_orders AS
SELECT * FROM orders_skewed WHERE status = 'pending';

PREPARE get_completed_orders AS
SELECT * FROM orders_skewed WHERE status = 'completed';

-- 4. Named vs unnamed prepared statements
/*
// Unnamed (automatically managed by driver)
await client.query('SELECT * FROM users WHERE id = $1', [123]);

// Named (manually managed)
await client.query({
  name: 'get-user',
  text: 'SELECT * FROM users WHERE id = $1'
}, [123]);

// Named statements persist across queries
// Unnamed may be automatically deallocated
*/

-- 5. Prepared statements and security
-- Prevents SQL injection

-- VULNERABLE (don't do this):
-- query = "SELECT * FROM users WHERE username = '" + userInput + "'";

-- SAFE (prepared statement):
PREPARE safe_query (VARCHAR) AS
SELECT * FROM users WHERE username = $1;

EXECUTE safe_query('anything; DROP TABLE users;--');
-- Treated as literal string, not executed

-- Application-level caching
/*
// Redis for query result caching
const redis = require('redis');
const client = redis.createClient();

async function getUser(id) {
  const cacheKey = `user:${id}`;

  // Check cache
  const cached = await client.get(cacheKey);
  if (cached) {
    return JSON.parse(cached);
  }

  // Query database with prepared statement
  const result = await pool.query(
    'SELECT * FROM users WHERE id = $1',
    [id]
  );

  const user = result.rows[0];

  // Cache result
  await client.setex(cacheKey, 3600, JSON.stringify(user));

  return user;
}
*/

-- View current prepared statements
SELECT
  name,
  statement,
  prepare_time,
  parameter_types::text[] AS param_types,
  from_sql
FROM pg_prepared_statements
ORDER BY prepare_time DESC;