> It would be cool if EXPLAIN ANALYZE also perhaps showed other query plans the planner considered but discarded, and why. Imagine if the planner tried multiple plans and adjusted itself accordingly.
The interesting point is in that regard MySQL is really better than PostgreSQL. MySQL can give you more execution plans it evaluated and tell you why it didn't use them (cost value is higher) and even tell you why it didn't use a specific index it. Combined with the enforcing specific indexes you can sometimes trick it into a more efficient query plan even if it believed to be worse.
But to be honest, PostgreSQL query planner is a lot more intelligent and does stupid things very seldom. And the ability to instruct PostgreSQL collecting more statistics on some attributes (https://www.postgresql.org/docs/13/sql-createstatistics.html) is a huge improvement to getting PostgreSQL make more intelligent plans.
What i really miss in PostgreSQL is:
* Seeing other query plans it discarded as you described to get a feeling how to hint PostgreSQL into a direction if the query planner is doing stupid things
* The ability to enforce specific query plans. The PostgreSQL devs stated they are against this but pg_hint_plan is really usefull and i was able to drastically improve some very complex queries.
The interesting point is in that regard MySQL is really better than PostgreSQL. MySQL can give you more execution plans it evaluated and tell you why it didn't use them (cost value is higher) and even tell you why it didn't use a specific index it. Combined with the enforcing specific indexes you can sometimes trick it into a more efficient query plan even if it believed to be worse.
But to be honest, PostgreSQL query planner is a lot more intelligent and does stupid things very seldom. And the ability to instruct PostgreSQL collecting more statistics on some attributes (https://www.postgresql.org/docs/13/sql-createstatistics.html) is a huge improvement to getting PostgreSQL make more intelligent plans.
What i really miss in PostgreSQL is:
* Seeing other query plans it discarded as you described to get a feeling how to hint PostgreSQL into a direction if the query planner is doing stupid things
* MySQLs query profile showing the execution time of multiple subtasks of the query: https://dev.mysql.com/doc/refman/8.0/en/show-profile.html
* The ability to enforce specific query plans. The PostgreSQL devs stated they are against this but pg_hint_plan is really usefull and i was able to drastically improve some very complex queries.