From $1 Placeholders to Executable Queries: Debugging the Parameters That Matter

PostgreSQL's pg_stat_statements is brilliant at telling you which query patterns are expensive. It is terrible at telling you why a specific execution was slow. The problem is right there in the query text: WHERE customer_id = $1. That $1 is normalized away, and with it goes the single most important piece of debugging information — the actual value that triggered the slow path.

The Problem

pg_stat_statements is the foundation of PostgreSQL query performance monitoring. It aggregates execution statistics per query template, grouping all executions of SELECT * FROM orders WHERE customer_id = $1 regardless of the parameter value. This is exactly what you want for identifying expensive query patterns — but it hides a critical detail.

A query might average 5ms across 100,000 calls but take 12 seconds for one specific customer. The customer with ID 847291 has 500,000 orders while the median customer has 15. The index scan that works perfectly for most customers degrades to near-sequential-scan performance for this outlier. The average hides the catastrophe.

You cannot reproduce this problem from pg_stat_statements alone. You see WHERE customer_id = $1 and the mean time, but not the parameter value that caused the outlier. Was it customer 847291? Or was it a date range query where $2 = '2020-01-01' pulled 5 years of data? Without the actual parameter values, you cannot run EXPLAIN ANALYZE to see what the planner does for that specific input.

The standard workaround is to search PostgreSQL logs. If log_min_duration_statement is configured, slow queries are logged with their parameters in a DETAIL line. But parsing log files to extract parameters, correlate them with query templates, and reconstruct executable queries is manual, tedious, and error-prone — exactly the kind of work that gets skipped during a production incident.

How to Detect It

PostgreSQL can log slow queries with their parameter values, but you need the right configuration:

-- Check if slow query logging is enabled
SHOW log_min_duration_statement;
-- -1 means disabled, 0 logs everything, positive value is milliseconds

-- Enable logging of queries slower than 500ms
ALTER SYSTEM SET log_min_duration_statement = 500;
SELECT pg_reload_conf();

When enabled, slow queries appear in the PostgreSQL log with a DETAIL line containing the parameter values:

LOG:  duration: 12847.234 ms  execute :
      SELECT * FROM orders WHERE customer_id = $1 AND order_date > $2
DETAIL:  parameters: $1 = '847291', $2 = '2020-01-01'

To reconstruct an executable query from this, you must manually substitute each parameter:

-- Manually reconstructed from log parsing
SELECT * FROM orders
WHERE customer_id = '847291'
  AND order_date > '2020-01-01';

This works for one query. For systematic analysis, you would need to parse the log files programmatically, match DETAIL lines to their query statements, and build executable queries for each slow execution. Most teams lack this tooling, so parameter values are effectively lost — the information exists in the logs but is inaccessible in practice.

Another approach is auto_explain, which logs the EXPLAIN plan for slow queries. This gives you the plan but still not the executable query you can paste into psql to reproduce:

-- Enable auto_explain for queries over 1 second
LOAD 'auto_explain';
SET auto_explain.log_min_duration = 1000;
SET auto_explain.log_analyze = true;

From Logs to Executable Queries

The missing piece in the PostgreSQL monitoring ecosystem is automated parameter extraction. The information already exists in your logs — the DETAIL line has everything you need. What is missing is the tooling to parse it at scale, match parameters to their templates, and produce ready-to-run queries.

A properly built extraction pipeline does three things:

1. Parses DETAIL lines from PostgreSQL logs, matching each parameter set to its query statement
2. Substitutes $N placeholders with actual values, producing executable SQL
3. Deduplicates and retains the most recent and most diverse parameter combinations per template

The result is that each query template accumulates a set of real-world parameter samples. When you need to debug a slow execution, you have the exact values that caused it — ready to paste into psql with EXPLAIN (ANALYZE, BUFFERS).

This is particularly powerful for queries where performance varies by orders of magnitude depending on the input. A query that runs in 5ms for 99.9% of parameter values but 12 seconds for one outlier cannot be diagnosed without that outlier value.

The prerequisite is a single PostgreSQL setting: log_min_duration_statement. Without it, no tool — manual or automated — can recover parameter values from normalized templates.

How to Fix It

Once you have an executable query with the problematic parameters, diagnose the plan:

-- Run EXPLAIN ANALYZE with the exact parameters from the sample
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM orders
WHERE customer_id = '847291'
  AND order_date > '2020-01-01';

Compare this plan to the same query with a typical parameter value:

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM orders
WHERE customer_id = '12345'
  AND order_date > '2020-01-01';

Common findings:

Skewed data distribution: Customer 847291 has 500,000 rows, the planner estimates 15 (the average). Fix with extended statistics or a partial index:

-- Extended statistics for correlated columns
CREATE STATISTICS orders_customer_date_stats
    ON customer_id, order_date FROM orders;
ANALYZE orders;

-- Partial index for high-volume customers
CREATE INDEX CONCURRENTLY idx_orders_high_volume
    ON orders (customer_id, order_date)
    WHERE customer_id IN (SELECT customer_id FROM orders
                          GROUP BY customer_id HAVING count(*) > 10000);

Date range too wide: $2 = '2020-01-01' pulls 5 years of data. The application may need pagination or the query needs a tighter default range.

Generic plan vs. custom plan: On PostgreSQL 12+, after 5 executions the planner may switch to a generic plan that is suboptimal for outlier parameter values. Check with plan_cache_mode = force_custom_plan as a diagnostic.

How to Prevent It

Ensure log_min_duration_statement is set to a reasonable threshold — 500ms to 1000ms is a good starting point. Too low floods the logs, too high misses important slow queries. This single setting is the prerequisite for parameter extraction; without it, no tool can recover parameter values from normalized templates.

Set log_parameter_max_length to at least 1024 (PostgreSQL 13+) to ensure long parameter values are not truncated in the log DETAIL line. Truncated parameters produce incomplete executable queries.

Review captured samples periodically to identify parameter patterns that consistently cause slow execution. If the same customer ID or date range appears across multiple slow samples, the problem is data skew rather than a missing index.