0

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.

3
  • 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.
    – SQLRaptor
    Jul 6, 2019 at 5:25
  • Yes, the optimizer is usually smarter than you are. Jul 8, 2019 at 7:16
  • yes, the optimizer is usually smarter, but always interesting to find its limits and push its boundaries!
    – Zeruno
    Jul 8, 2019 at 11:38

1 Answer 1

0
-- 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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.