Question 169073 in PostgreSQL Question posted in
The official documentation can be found here.

How do I get a PostgreSQL exec plan w/params?

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?

Tags:

2

Answers


  1. 0

    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.

    Login or Signup to reply.
  2. 0

    The issue you’re encountering with obtaining the execution plan for parameterized queries in PostgreSQL is due to how prepared statements work in JDBC. The JDBC driver tries to optimize the query execution by precompiling it without specific parameter values, which can cause conflicts when trying to explain the prepared statement.

    To work around this issue and retrieve the execution plan for parameterized queries, you can use the PREPARE statement directly in PostgreSQL instead of relying solely on JDBC’s prepared statements. Here’s an example of how you can modify your code to achieve this:

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

PreparedStatement ps1 = conn.prepareStatement(
  "PREPARE x AS SELECT * FROM documents WHERE content = $1");
ps1.execute();

PreparedStatement ps2 = conn.prepareStatement(
  "EXECUTE x ('Very long content here...')");
ResultSet rs = ps2.executeQuery();

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

    In this modified code, we replace the usage of prepare and explain within the JDBC code with the corresponding PostgreSQL statements PREPARE and EXECUTE. By directly using PREPARE to create the prepared statement and EXECUTE to execute it with the specific parameter value, you should be able to retrieve the execution plan successfully.

    By using the PREPARE and EXECUTE statements in PostgreSQL, you can overcome the limitation of obtaining the execution plan for parameterized queries through JDBC’s prepared statements.

    Certainly! Here’s the continuation of the code snippet:

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

PreparedStatement ps1 = conn.prepareStatement(
  "PREPARE x AS SELECT * FROM documents WHERE content = $1");
ps1.execute();

PreparedStatement ps2 = conn.prepareStatement(
  "EXECUTE x ('Very long content here...')");
ResultSet rs = ps2.executeQuery();

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

rs.close();
ps2.close();
ps1.close();
conn.close();

    In the updated code, after executing the EXECUTE statement with the parameter value, you can iterate over the result set and process the execution plan accordingly. Remember to close the result set, prepared statements, and connection once you have finished working with them to release any associated resources.

    By using the PREPARE and EXECUTE statements directly in PostgreSQL, you should be able to retrieve the execution plan for your parameterized queries successfully, even for non-trivial cases. This approach allows you to bypass the limitation you encountered with JDBC’s prepared statements.

    Please ensure you replace the "..." placeholders in the connection string with the appropriate connection details for your PostgreSQL database.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search