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.
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.