1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104

# Benchmark: Prepared vs Ad-Hoc Query Performance
 
import time
import psycopg2
from psycopg2 import sql
import statistics
 
def benchmark_adhoc(conn, iterations=10000):
    """Benchmark ad-hoc queries (string formatting)."""
    cursor = conn.cursor()
    times = []
    
    for i in range(iterations):
        user_id = i % 100000  # Vary the parameter
        
        start = time.perf_counter()
        # WARNING: Don't do this in production (SQL injection risk)
        cursor.execute(f"SELECT * FROM users WHERE id = {user_id}")
        cursor.fetchone()
        elapsed = time.perf_counter() - start
        
        times.append(elapsed * 1000)  # Convert to ms
    
    return {
        'mean_ms': statistics.mean(times),
        'p99_ms': sorted(times)[int(len(times) * 0.99)],
        'qps': iterations / sum(times) * 1000
    }
 
def benchmark_prepared(conn, iterations=10000):
    """Benchmark prepared statements."""
    cursor = conn.cursor()
    times = []
    
    # Prepare once (in real apps, this happens at connection init)
    cursor.execute(
        "PREPARE user_lookup(int) AS "
        "SELECT * FROM users WHERE id = $1"
    )
    
    for i in range(iterations):
        user_id = i % 100000
        
        start = time.perf_counter()
        cursor.execute("EXECUTE user_lookup(%s)", (user_id,))
        cursor.fetchone()
        elapsed = time.perf_counter() - start
        
        times.append(elapsed * 1000)
    
    cursor.execute("DEALLOCATE user_lookup")
    
    return {
        'mean_ms': statistics.mean(times),
        'p99_ms': sorted(times)[int(len(times) * 0.99)],
        'qps': iterations / sum(times) * 1000
    }
 
def benchmark_native_prepared(conn, iterations=10000):
    """Benchmark using driver's native prepared statement support."""
    times = []
    
    # Use parameterized query (driver handles preparation)
    query = "SELECT * FROM users WHERE id = %s"
    
    for i in range(iterations):
        user_id = i % 100000
        cursor = conn.cursor()
        
        start = time.perf_counter()
        cursor.execute(query, (user_id,))
        cursor.fetchone()
        elapsed = time.perf_counter() - start
        
        times.append(elapsed * 1000)
        cursor.close()
    
    return {
        'mean_ms': statistics.mean(times),
        'p99_ms': sorted(times)[int(len(times) * 0.99)],
        'qps': iterations / sum(times) * 1000
    }
 
# Run benchmarks
conn = psycopg2.connect("dbname=benchmark user=postgres")
 
print("Warming up connection pool...")
benchmark_adhoc(conn, iterations=100)
 
print("\nBenchmark Results:")
print("-" * 50)
 
adhoc = benchmark_adhoc(conn, iterations=10000)
print(f"Ad-Hoc Queries:")
print(f"  Mean: {adhoc['mean_ms']:.3f}ms, P99: {adhoc['p99_ms']:.3f}ms")
print(f"  Throughput: {adhoc['qps']:.0f} qps")
 
prepared = benchmark_prepared(conn, iterations=10000)
print(f"\nPrepared Statements:")
print(f"  Mean: {prepared['mean_ms']:.3f}ms, P99: {prepared['p99_ms']:.3f}ms")
print(f"  Throughput: {prepared['qps']:.0f} qps")
 
improvement = prepared['qps'] / adhoc['qps']
print(f"\nImprovement: {improvement:.1f}x throughput increase")

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52

-- PostgreSQL JIT Performance Analysis
 
-- Enable JIT and set thresholds
SET jit = on;
SET jit_above_cost = 100000;        -- JIT for queries costing > this
SET jit_inline_above_cost = 500000;  -- Inline functions above this cost
SET jit_optimize_above_cost = 500000; -- Apply optimizations above this
 
-- Analyze a query with JIT timing breakdown
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) 
SELECT 
    l_returnflag,
    l_linestatus,
    SUM(l_quantity) as sum_qty,
    SUM(l_extendedprice) as sum_base_price,
    SUM(l_extendedprice * (1 - l_discount)) as sum_disc_price,
    AVG(l_quantity) as avg_qty,
    AVG(l_extendedprice) as avg_price,
    COUNT(*) as count_order
FROM lineitem
WHERE l_shipdate <= DATE '1998-12-01' - INTERVAL '90 days'
GROUP BY l_returnflag, l_linestatus
ORDER BY l_returnflag, l_linestatus;
 
-- Sample output shows JIT timing:
-- JIT:
--   Functions: 21
--   Options: Inlining true, Optimization true, Expressions true, Deforming true
--   Timing: Generation 5.234 ms, Inlining 18.456 ms, Optimization 89.123 ms,
--           Emission 45.678 ms, Total 158.491 ms
-- Execution Time: 2134.567 ms
 
-- Compare with JIT disabled
SET jit = off;
-- Re-run query... Execution Time: 4567.890 ms
 
-- JIT benefit: 4567 / 2134 = 2.14x speedup
-- Note: JIT time (158ms) is small compared to execution savings (2433ms)
 
-- Query statistics including JIT metrics
SELECT 
    substring(query, 1, 60) as query_preview,
    calls,
    mean_exec_time::numeric(10,2) as avg_ms,
    jit_functions as jit_funcs,
    jit_generation_time::numeric(10,2) as jit_gen_ms,
    jit_optimization_time::numeric(10,2) as jit_opt_ms,
    jit_emission_time::numeric(10,2) as jit_emit_ms
FROM pg_stat_statements
WHERE jit_functions > 0
ORDER BY jit_generation_time DESC
LIMIT 10;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56

-- SQL Server: Plan Cache Impact Analysis
 
-- 1. Overall plan cache efficiency
SELECT 
    objtype as object_type,
    COUNT(*) as cached_plans,
    SUM(usecounts) as total_uses,
    SUM(size_in_bytes) / 1024 / 1024 as cache_size_mb,
    AVG(usecounts) as avg_reuse_count,
    SUM(usecounts) * 1.0 / COUNT(*) as reuse_ratio
FROM sys.dm_exec_cached_plans
GROUP BY objtype
ORDER BY total_uses DESC;
 
-- 2. Cache hit ratio from performance counters
SELECT 
    counter_name,
    cntr_value
FROM sys.dm_os_performance_counters
WHERE object_name LIKE '%Plan Cache%'
    AND counter_name IN (
        'Cache Hit Ratio',
        'Cache Hit Ratio Base',
        'Cache Object Counts',
        'Cache Pages'
    );
 
-- 3. Compilation time analysis
-- (Requires Query Store to be enabled)
SELECT 
    SUM(rs.count_executions) as total_executions,
    SUM(p.count_compiles) as total_compiles,
    (SUM(rs.count_executions) - SUM(p.count_compiles)) as cache_hits,
    100.0 * (SUM(rs.count_executions) - SUM(p.count_compiles)) 
        / SUM(rs.count_executions) as cache_hit_pct,
    SUM(p.count_compiles * rs.avg_compile_duration) / 1000000.0 
        as total_compile_sec,
    SUM(p.count_compiles * rs.avg_compile_duration) / 1000000.0 
        / SUM(p.count_compiles) as avg_compile_sec
FROM sys.query_store_plan p
JOIN sys.query_store_runtime_stats rs ON p.plan_id = rs.plan_id;
 
-- 4. Queries that would benefit from better caching
-- (High compile count relative to execution count)
SELECT TOP 20
    qt.query_sql_text,
    p.count_compiles,
    rs.count_executions,
    (p.count_compiles * 1.0 / rs.count_executions) as compile_ratio,
    rs.avg_compile_duration / 1000.0 as avg_compile_ms
FROM sys.query_store_plan p
JOIN sys.query_store_query q ON p.query_id = q.query_id
JOIN sys.query_store_query_text qt ON q.query_text_id = qt.query_text_id
JOIN sys.query_store_runtime_stats rs ON p.plan_id = rs.plan_id
WHERE rs.count_executions > 100
ORDER BY compile_ratio DESC;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77

# Anti-Patterns in Query Optimization
 
import psycopg2
 
# ==========================================
# ANTI-PATTERN 1: Preparing inside the loop
# ==========================================
def process_orders_WRONG(order_ids: list):
    """
    WRONG: Prepares statement for each order.
    Worse than ad-hoc because of PREPARE + EXECUTE overhead.
    """
    conn = psycopg2.connect(...)
    cursor = conn.cursor()
    
    for order_id in order_ids:
        # Preparing inside loop - defeats the purpose!
        cursor.execute(
            "PREPARE get_order AS SELECT * FROM orders WHERE id = $1"
        )
        cursor.execute("EXECUTE get_order(%s)", (order_id,))
        cursor.execute("DEALLOCATE get_order")
        # ... process order
 
def process_orders_CORRECT(order_ids: list):
    """
    CORRECT: Prepare once, execute many times.
    """
    conn = psycopg2.connect(...)
    cursor = conn.cursor()
    
    # Prepare once
    cursor.execute(
        "PREPARE get_order(int) AS SELECT * FROM orders WHERE id = $1"
    )
    
    # Execute many times
    for order_id in order_ids:
        cursor.execute("EXECUTE get_order(%s)", (order_id,))
        # ... process order
    
    # Deallocate when done
    cursor.execute("DEALLOCATE get_order")
 
 
# ==========================================
# ANTI-PATTERN 2: Parameterized SQL Injection
# ==========================================
def search_products_WRONG(table_name: str, category: str):
    """
    WRONG: User input in non-parameterizable position.
    Still vulnerable despite using parameters for category!
    """
    conn = psycopg2.connect(...)
    cursor = conn.cursor()
    
    # table_name from user input - SQL injection!
    query = f"SELECT * FROM {table_name} WHERE category = %s"
    cursor.execute(query, (category,))  # Category is safe, table is not!
    return cursor.fetchall()
 
def search_products_CORRECT(table_name: str, category: str):
    """
    CORRECT: Whitelist non-parameterizable inputs.
    """
    ALLOWED_TABLES = {'products', 'archived_products', 'featured_products'}
    
    if table_name not in ALLOWED_TABLES:
        raise ValueError(f"Invalid table: {table_name}")
    
    conn = psycopg2.connect(...)
    cursor = conn.cursor()
    
    # Safe because table_name is whitelisted
    query = f"SELECT * FROM {table_name} WHERE category = %s"
    cursor.execute(query, (category,))
    return cursor.fetchall()