How do I get a PostgreSQL execution plan with parameters?

My application has a myriad of queries and I wanted to get the execution plan of some of them. Most if not
all of the queries have multiple parameters and I can't find how to get the execution plan for any non-trivial query in PostgreSQL.

The real cases are much more involved but here's a simple, representative case of a query (just one parameter for simplicity):

Connection conn = DriverManager.getConnection("...", "...", "...");

PreparedStatement ps1 = conn.prepareStatement(
  "prepare x as select * from documents where content = $1");
ps1.execute();

PreparedStatement ps2 = conn.prepareStatement(
  "explain (format json) execute x (?)");
ps2.setString(1, "Very long content here..."); // Binds the parameter
ResultSet rs = ps2.executeQuery(); // Error here!

while (rs.next()) {
  System.out.println(rs.getString(1));
}

When I run this, I get the error:

> ERROR: there is no parameter $1
> Position: 34

If I hardcode the parameter (e.g. replacing $1 with 'a') everything works well and I get a plan. However, if I try to use a JDBC parameter it doesn't work. Hardcoding the parameter is not realistic for my use case since it can be a massive parameter or may not be rendered correctly as String (e.g. float value).

I also tried using null instead of ? and it doesn't crash, but
it returns an execution plan that is wrong; it seems it somehow short-circuits the logic and returns something totally off reality.

What am I doing wrong?

You don't need to replace the $1 with the bind variable value, but you can't apparently use the bind variable in the explain execute statement - here it must be stated.

So this sequence works fine (pseudocode)

prep = con.prepareStatement("prepare x(integer) as select id, pad from jdbn.document where id = $1")
prep.execute();

stmt = con.prepareStatement("explain execute x (42)")
rs = stmt.executeQuery()

As described in the previously marked as duplicated question you get for first few execution a custom plan (i.e. you see the variable in the condition, here id = 42) and finaly a generic plan (i.e. with a predicate id = $1)

Your trick with null fails as PostgreSql knows that id = null returns nothing (I'm using vague formulation to avoid to be neither true nor false) and makes a dummy plan e.g. One-Time Filter: false

You may force to produce a generic plan with the plan_cache_mode parameter

con.createStatement().execute("set plan_cache_mode = force_generic_plan")

in which case the the first explained plan is generic. It seem that with this setting the null parameter does not lead to a dummy plan (but I'm not sure if there are not other caveats).

So I'd say you should always define the data type of the variable prepare x(integer) ... but I'm not sure and have no experience if you can get a meaningfull execution plan with the combination of force_generic_plan and passing null (similar to Oracle functionality) or if you realy must pass representation sample value(s) or the parameter(s).

Final Note all the discussion here is focused on well designed prepared statements, i.e. any possible value passed will lead to a single same excution plan. For other statements it is not meaningfull, as there is no single execution plan.

you can try this line

PreparedStatement ps1 = conn.prepareStatement(
  "prepare x as select * from documents where content = '"+ 1 +"'");
ps1.execute();

instead of

   PreparedStatement ps1 = conn.prepareStatement(
      "prepare x as select * from documents where content = $1");
    ps1.execute();