The same statement takes 20ms in psql and four seconds from the application. Where do you look?

Prove the time is in the database first

The application's stopwatch measures far more than execution. It includes waiting for a connection from the pool, the round trip, the driver decoding every row into objects, and any ORM hydration behind that. So before touching a query, split the four seconds.

pg_stat_statements settles it in one look. Find the entry for the statement — the view carries the normalised query text alongside its queryid, so quote both when you write this up rather than a bare identifier nobody can resolve later — and compare mean_exec_time against your 20ms. If the database's own view of the statement says 20ms, the database is not slow and no amount of index work will help. What you are looking for is then somewhere else: pool acquisition wait, an N+1 pattern where the page issues four hundred of these rather than one, or the driver fetching a hundred thousand rows the application discards after the first page.

That last one is specific enough to check directly. The PostgreSQL JDBC driver materialises the entire result set by default; a cursor-based fetch requires setFetchSize and autocommit switched off, and without both you pay for every row the query can produce regardless of how many you read. A statement that returns 200,000 rows is 20ms of execution and seconds of transfer and object construction, and it will look fast in psql too if you have a pager truncating the output.

The difference the planner sees

If the time genuinely is inside the database, the leading explanation is that your psql run and the application's run are not the same query. You typed literals; the application sent bind parameters.

-- What you ran: the planner knows the value, so it can consult the
-- most-common-values list and the histogram for this exact status.
SELECT * FROM shipment WHERE tenant_id = 4412 AND status = 'IN_TRANSIT';

-- What the application sent: the planner may have to choose without
-- knowing either value.
SELECT * FROM shipment WHERE tenant_id = $1 AND status = $2;

This matters most on skewed data. If 99% of rows are DELIVERED and 0.1% are IN_TRANSIT, the literal lets the planner see that the predicate is highly selective and choose an index scan; without it, PostgreSQL falls back to an average estimate over the column's distribution and may cost a sequential scan as cheaper. Same statement, same data, different plan, and the difference is entirely in what the planner was told.

The mechanism is worth stating precisely because it is unusual. PostgreSQL replans a prepared statement with the actual parameter values for its first five executions, then compares the average cost of those custom plans with the cost of a generic plan built without the values; if the generic plan is not more expensive it switches to it and reuses it from then on. So the pathology has a signature that is diagnostic on its own: the query is fast for a while after a deploy and then permanently slow, and it goes fast again briefly after every restart or after the connection pool recycles a connection. plan_cache_mode is the lever — setting it to force_custom_plan, ideally on the specific statement's session rather than globally, makes the planner use the values every time at the cost of planning work per execution.

Two secondary notes. This only applies when the statement is genuinely prepared server-side: the JDBC driver switches from a simple to a server-prepared statement after prepareThreshold executions of the same SQL on the same connection, five by default, which is another reason the slowdown appears after warm-up rather than immediately. And a pooler in transaction-pooling mode may prevent server-side prepared statements from being reused at all, in which case you never see this problem and you have a different one.

Capture the plan the application actually got

Reproducing by hand is unreliable, so stop trying. auto_explain logs the plan for any statement exceeding a duration you choose, from inside the session that ran it, with the parameters it ran with.

LOAD 'auto_explain';                          -- or add to shared_preload_libraries
SET auto_explain.log_min_duration = '500ms';
SET auto_explain.log_analyze = on;            -- real row counts, at some overhead
SET auto_explain.log_nested_statements = on;  -- catches statements inside functions

This is the tool that ends the argument, because it gives you the slow plan rather than a plan you hope resembles it. log_nested_statements earns its place whenever any logic lives in a function or a trigger, since those never appear as top-level statements and are invisible to everything else.

If you want to reproduce by hand instead, do it properly: PREPARE the statement, EXECUTE it six times so the generic plan takes over, and EXPLAIN EXECUTE the seventh. Running EXPLAIN with the literals inlined reproduces your fast case and tells you nothing.

The session is not the same session

Several differences have nothing to do with plans and everything to do with the environment the two runs happen in.

search_path is the one that catches people out most sharply, because in a schema-per-tenant or a shadow-schema deployment your psql session and the application's role can resolve the same unqualified table name to different tables with different sizes and different indexes. Compare SHOW search_path in both.

work_mem decides whether a sort or a hash spills to disk, and it is frequently set per role or per pooler configuration. A sort that fits in memory in your session and spills in the application's is exactly a 20ms-versus-4s difference, and EXPLAIN (ANALYZE, BUFFERS) shows it as temp read and temp written on the offending node. Check also for a stray .psqlrc setting planner flags, and for connection-level settings applied by the pool.

Data type mismatch belongs here too, and it is a plan problem with a session-level cause. If the driver sends a parameter as text where the column is bigint or uuid, the comparison acquires a cast and may stop being usable as an index condition. In PostgreSQL the JDBC option stringtype=unspecified and, better, binding with the correct type via setObject are the fixes; the tell in the plan is a cast appearing on the column side of the predicate rather than the parameter side.

Cache warmth is the mundane possibility to eliminate early. You almost certainly ran the query in psql after the application had already pulled its pages into shared buffers. Run it twice, and compare shared hit against shared read in the BUFFERS output; if the first run reads and the second hits, you measured a cold cache, not a plan.

The same problem under other engines

Interviewers often ask this without naming an engine, and the vocabulary differs enough to be worth having.

SQL Server calls it parameter sniffing: the plan is compiled for the parameter values supplied on the first execution and then cached and reused for every subsequent set of values, so a plan optimised for a tiny tenant gets applied to the largest one. The levers are OPTIMIZE FOR UNKNOWN, OPTION (RECOMPILE) on the statement, or in recent versions the parameter-sensitive plan optimisation feature that maintains several plans for one statement.

Oracle peeks at the bind values on the first hard parse and has adaptive cursor sharing to build additional child cursors when a bind-sensitive predicate turns out to produce very different row counts. MySQL's optimiser re-optimises on each execution by default, so it is much less exposed to this specific failure — a useful thing to say, because it stops the answer sounding like it was memorised for one product.

Split the four seconds before you tune anything, then check whether the application's version of the statement gives the planner the values yours did. A plan built without the literals is a different plan, and it is the difference behind most reports of this shape.