CODEWITHSUNDEEP
postgresqlquery-optimizationrdbmsquery-planner
Ymi
Ymi

Reputation: 768

PostgreSQL Query Planner/Optimizer: Is there a way to get candidate plans?

In PostgreSQL, we can use "EXPLAIN ANALYZE" on a query to get the query plan of a given SQL Query. While this is useful, is there anyway that we are able to get information on other candidate plans that the optimizer generated (and subsequently discarded)?

This is so that we can do an analysis ourselves for some of the candidates (for e.g. top 3) generated by the DBMS.

Upvotes: 1

Views: 536

Answers (1)

jjanes
jjanes

Reputation: 44192

No. The planner discards incipient plans as early as it can, before they are even completely formed. Once it decides a plan can't be the best, it never finishes constructing it, so it can't display it.

You can usually use the various enable_* settings or the *_cost settings to force it to make a different choice and show the plan for that, but it can be hard to control exactly what that different choice is.

You can also temporarily drop an index to see what it would do without that index. If you DROP an index inside a transaction, then do the EXPLAIN, then ROLLBACK the transaction, it will rollback the DROP INDEX so that the index doesn't need to be rebuilt, it will just be revived. But be warned that DROP INDEX will take a strong lock on the table and hold it until the ROLLBACK, so this method is not completely free of consequences.

If you just want to see what the other plan is, you just need EXPLAIN, not EXPLAIN ANALYZE. This is faster and, if the statement has side effects, also safer.

Upvotes: 2

Related Questions