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

Explain Plan in PostgreSQL does not execute when provided with placeholders unlike Oracle

Example at Oracle:

explain plan for select * from sample_table where column_name = :1

This will return query plan with detailed steps and cost in a tabular form.

Example at PostgreSQL

explain (format yaml) select * from sample_table where column_name = $1

This will end up returning an error which asks for parameters.

explain (format yaml) select * from sample_table where column_name = $$$$

This will end up returning an error for timestamp and integer

explain (format yaml) select * from sample_table where column_name = null

This will end up returning incomplete plan with no filter information as we are passing null.

Note: Aware that it might be looking for actual parameters.

Could anyone please guide on any appropriate value which could be used for character varying, text, integer and timestamp that could give a complete plan? Point is we cannot pass the actual parameters due to data concerns.

Tags:

2

Answers


  1. 0

    It is not so easy to get a generic plan in PostgreSQL. You can do it by setting plan_cache_mode = force_generic_plan, creating a prepared statement and calling EXPLAIN EXECUTE ... while supplying NULL values.

    You can find the procedure described in my article.

    I have submitted a patch for PostgreSQL v16 that adds that functionality to core. If you want that to happen, you could review the patch or express your interest on the mailing list.

    Login or Signup to reply.
  2. 0

    You can get the execution plan for a specific value if you use a prepared statement:

    prepare pstmt(integer) 
as 
select * 
from sample_table 
where column_name = $1;

    Then get the plan for a specific value using:

    explain (analyze, buffers) 
execute pstmt(42);
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search