Building and Executing a Query Execution Plan Manually in PostgreSQL

Question

An SQL query goes through multiple stages to be executed. This is a wide topic, but in summary, multiple execution strategies are proposed for each query, they are evaluated, and one is selected for execution, and finally, it is executed.

We can see an SQL's query plan using PostgreSQL's EXPLAIN ANALYZE here https://www.postgresql.org/docs/current/using-explain.html.

I was wondering if it is possible to build the plan manually and then execute it. In other words, as a user, I won't use SQL nor the query optimizer features offered by PostgreSQL. Instead, I will manually construct the query plan and execute that. I would figure that PostgreSQL would have such features for debugging or an extension would exist, but I can't find such functionality.

• SQLRaptor: AFAIK PostgreSQL doesn't support query hints nor other types of plan forcing. There are a few optimizer parameters you can tweak, but in general I would advise against it.
• Laurenz Albe: Yes, the optimizer is usually smarter than you are.
• Zeruno: yes, the optimizer is usually smarter, but always interesting to find its limits and push its boundaries!

Answer 1

-- Create a custom query plan tree
DECLARE custom_plan pg_plan;
BEGIN;
custom_plan := pg_plan(
 'SELECT * FROM your_table WHERE your_column = 1ドル',
 'SELECT * FROM your_table WHERE your_column = 1ドル',
 'SELECT * FROM your_table WHERE your_column = 1ドル'
);
-- Execute the custom plan
EXECUTE custom_plan(42);
-- You can pass parameter values as needed
-- Release the plan when done
END;