SQL

Debugging Parameterized SQL Query Plans Through SSH Tunnels

Debugging slow PostgreSQL queries from DataGrip or DBeaver through an SSH bastion often produces misleading query plans or hangs local tools completely. Running EXPLAIN (ANALYZE, BUFFERS) on a query with substituted literal values masks parameter-sniffing bugs, while running ad-hoc raw queries against remote staging instances through naive local port forwards risks dropped connections and untracked writes.

Setting up a reproducible, parameterized diagnostic workflow requires three things: an explicit local loopback tunnel that handles connection drops cleanly, a dedicated scratchpad configured for session-scoped parameter bindings, and a structured EXPLAIN format that displays real buffer hits rather than cached estimates.

The Default Problem: Parameter Masking and Tunnel Drops

When you paste an application query from your backend logs into DataGrip, your first instinct is to replace $1 and $2 with string literals so the SQL parser accepts the text.

text
-- Application query in backend logs:
SELECT id, org_id, payload, created_at 
FROM event_logs 
WHERE org_id = $1 AND status = $2 
ORDER BY created_at DESC LIMIT 50;

-- What gets pasted into the IDE scratchpad:
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, org_id, payload, created_at 
FROM event_logs 
WHERE org_id = 'org_8492019a' AND status = 'pending' 
ORDER BY created_at DESC LIMIT 50;

When you execute that snippet, the planner optimizes for those exact literals. If status = 'pending' matches 0.01% of rows in your staging dataset, the planner picks a neat Index Scan on idx_event_logs_org_status.

In your application runtime, your connection pool (such as PgBouncer or native drivers like pgx or asyncpg) prepares the statement. The engine evaluates the query as a generic plan or compiles custom plans against untyped placeholders. If the table statistics show status is heavily skewed toward 'completed', the generic plan might select a Bitmap Heap Scan or a parallel Seq Scan. The literal-substituted query in your scratchpad says the query takes 4 milliseconds, but production p99 latency is sitting at 3.2 seconds.

At the same time, if your tunnel to the target database uses a plain ssh -L 5432:db-internal:5432 user@bastion command, any transient packet drop or laptop sleep causes DataGrip or DBeaver to freeze. The IDE JDBC driver keeps polling an orphaned socket, blocking the interface thread until the default TCP timeout triggers minutes later.

Setting Up a Deterministic Tunnel with Strict Loopback Binding

Do not forward traffic to localhost:5432 if you run PostgreSQL locally on your machine. Bind the local endpoint to an explicit loopback alias on 127.0.0.2 or a non-standard local port. Ensure the SSH client sends server keepalive packets and drops cleanly on network resets.

Create a dedicated connection profile in your local ~/.ssh/config file:

ini
Host db-bastion-tunnel
    HostName bastion.internal.net
    User devops
    IdentityFile ~/.ssh/id_ed25519
    IdentitiesOnly yes
    ServerAliveInterval 15
    ServerAliveCountMax 3
    ExitOnForwardFailure yes
    LocalForward 127.0.0.1:54331 rds-read-replica.internal.net:5432

Launch the tunnel in your terminal using the background control flags:

bash
ssh -f -N -T db-bastion-tunnel

-f directs the SSH client to fork into the background before command execution. -N prevents remote command execution so no shell spawns. -T disables pseudo-terminal allocation.

Verify the local port is open and restricted exclusively to the loopback interface:

powershell
# Windows PowerShell validation
Get-NetTCPConnection -LocalPort 54331 | Select-Object LocalAddress, LocalPort, State, OwningProcess
bash
# macOS and Linux validation
ss -tulpn | grep 54331 || lsof -iTCP:54331 -sTCP:LISTEN

The output must show 127.0.0.1:54331 in the LISTEN state. Now configure your IDE connection string to point to 127.0.0.1, port 54331. In DataGrip or IntelliJ, open Data Sources and Drivers, set Host to 127.0.0.1, set Port to 54331, and uncheck Auto-sync. In the Advanced tab, set loginTimeout to 5 and socketTimeout to 30. This prevents queries and stale UI operations from hanging indefinitely if your laptop drops the Wi-Fi link.

Parameterized Scratchpads: Emulating Prepared Execution

To reproduce what your application experiences, do not inline string literals. Force PostgreSQL to evaluate execution paths using generic and custom plan semantics through PREPARE and EXECUTE commands directly inside your scratchpad.

Open a fresh SQL console in DataGrip or DBeaver. Make sure the scratchpad transaction mode is set to Manual rather than Auto-commit.

Write the parameter test sequence:

sql
-- Step 1: Discard any previous test plan in this session
DEALLOCATE ALL;

-- Step 2: Prepare the parameterized query as the app driver does
PREPARE bench_query (text, text) AS
SELECT id, org_id, payload, created_at 
FROM event_logs 
WHERE org_id = $1 AND status = $2 
ORDER BY created_at DESC LIMIT 50;

-- Step 3: Run the query 5 times to exhaust the custom plan threshold
-- (PostgreSQL switches to a generic plan on the 6th run if cheaper)
EXECUTE bench_query('org_8492019a', 'pending');
EXECUTE bench_query('org_8492019a', 'pending');
EXECUTE bench_query('org_8492019a', 'pending');
EXECUTE bench_query('org_8492019a', 'pending');
EXECUTE bench_query('org_8492019a', 'pending');

-- Step 4: Inspect the plan PostgreSQL actually settled on
EXPLAIN (ANALYZE, BUFFERS, SETTINGS, WAL)
EXECUTE bench_query('org_8492019a', 'pending');

If you use DataGrip, you can also leverage its native parameter prompt system for rapid tests without hardcoding the EXECUTE statement values. When you use the syntax :variable_name, DataGrip pauses execution and provides an inline data input form:

sql
-- In DataGrip scratchpad:
EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT id, org_id, payload, created_at 
FROM event_logs 
WHERE org_id = :target_org AND status = :target_status 
ORDER BY created_at DESC LIMIT 50;

To configure DataGrip parameter extraction, go to Settings | Tools | Database | User Parameters. Check Enable in-scripts parameter execution and make sure the regex includes (?<!:):(?P<name>[\w$]+). When triggered, DataGrip injects values using JDBC prepared parameter bindings instead of doing naive textual replacements.

Visualizing Buffer Cache Behavior, Not Just Milliseconds

Execution times on staging replicas are often noisy due to variable host loads, dirty page writes, and cold shared buffers. If a table sits entirely in the PostgreSQL shared buffer cache, a sequential scan looks fast during local testing but crashes under production concurrent I/O load.

Run your explain queries with explicit buffer outputs and format them as JSON. JSON output lets modern IDEs map the raw statistics into their internal graphical tree viewers:

sql
EXPLAIN (ANALYZE, BUFFERS, VERBOSE, SETTINGS, FORMAT JSON)
SELECT id, org_id, payload, created_at 
FROM event_logs 
WHERE org_id = 'org_8492019a' AND status = 'pending' 
ORDER BY created_at DESC LIMIT 50;

In DataGrip, right-click the JSON result block and choose Show Plan Visualization (shortcut: Ctrl+Alt+Shift+P on Windows/Linux, Cmd+Alt+Shift+P on macOS).

Look at the raw node properties rather than only watching the cost metrics:

text
"Node Type": "Index Scan",
"Relation Name": "event_logs",
"Shared Hit Blocks": 412,
"Shared Read Blocks": 1890,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"I/O Read Time": 42.152,

Here is how to interpret those metrics:

  • Shared Hit Blocks: Pages found already in PostgreSQL shared_buffers memory.
  • Shared Read Blocks: Pages pulled from OS filesystem cache or directly from physical NVMe storage.
  • I/O Read Time: Real wall-clock time spent waiting on kernel reads.

If your query plan shows low total time but massive Shared Hit Blocks on a Seq Scan node, your query is an I/O bomb waiting to happen. The moment those pages get pushed out of memory by background jobs in production, the node latency will spike by an order of magnitude.

Two Critical Gotchas

1. Read-Only Transaction Safety on Forwarded Tunnels

Tunneling to a shared environment can allow accidental destructive actions. It is trivial to highlight the wrong statement in a scratchpad and run an unindexed UPDATE or DELETE.

Force your IDE session to open exclusively in read-only mode by altering your scratchpad session defaults at the very top of your file:

sql
-- Apply to the immediate database session
SET SESSION CHARACTERISTICS AS TRANSACTION READ ONLY;

-- Verify safety level
SHOW transaction_read_only;

Output:

text
 transaction_read_only 
-----------------------
 on
(1 row)

If you attempt any write while testing query logic:

sql
DELETE FROM event_logs WHERE status = 'expired';

PostgreSQL aborts immediately:

text
[25006] ERROR: cannot execute DELETE in a read-only transaction

Keep this statement at the very top of your scratchpad files. If you disconnect or recreate the connection, execute it first before debugging anything.

2. PgBouncer Transaction Pooling Breaks PREPARE

If your tunnel points to an internal RDS proxy, Supabase pooler, or PgBouncer instance operating in transaction mode (rather than session mode), PREPARE bench_query will throw an error on the subsequent EXECUTE:

text
ERROR: prepared statement "bench_query" does not exist

This happens because the PREPARE was sent over connection A, and your next EXECUTE query went through connection B in the pool.

When debugging through a pooler where you cannot switch the listener to session mode, avoid using PREPARE. Instead, run anonymous code blocks with bound local variables. This forces PostgreSQL to evaluate the query within a single transactional execution frame while keeping parameters separated from the query tree:

sql
DO $
DECLARE
    v_org_id text := 'org_8492019a';
    v_status text := 'pending';
    v_rec record;
BEGIN
    FOR v_rec IN 
        SELECT id, org_id, payload, created_at 
        FROM event_logs 
        WHERE org_id = v_org_id AND status = v_status 
        ORDER BY created_at DESC LIMIT 50
    LOOP
        -- Process or null loop to measure engine execution
        NULL;
    END LOOP;
END $;

To view the plan inside an anonymous block, enable auto_explain in the local connection context if your role has adequate privileges:

sql
LOAD 'auto_explain';
SET auto_explain.log_min_duration = 0;
SET auto_explain.log_analyze = true;
SET auto_explain.log_buffers = true;
SET auto_explain.log_nested_statements = true;

The execution plan will now write directly into your server engine log output with its actual parameter substitutions, sidestepping transaction pooler statement boundaries entirely.

More in SQL