I run applications on both and I’ve found that “optimize your queries” is a little different based on which database you’re talking to.
They're both cost-based optimizers. They estimate how much work each execution plan will take and pick what they think is the cheapest. That's where the similarities start to fade.
The biggest difference I've noticed is joins.
Postgres has three main join strategies: nested loops, hash joins and merge joins. MySQL spent most of its life with nested loops only. It added hash joins in 8.0.18 (initially for equality joins where no usable index existed), but it still doesn't have merge joins.
That changes how forgiving each engine is. Join two large unindexed tables and Postgres still has more options. MySQL tends to lean much harder on good indexes because the optimizer has fewer ways out.
The next difference is statistics, and I think this sits behind a lot of bad execution plans.
Postgres gathers statistics with ANALYZE, sampling real table data and building histograms the planner can use. InnoDB traditionally estimates cardinality from a small sample of index pages instead, which I've seen produce some wildly optimistic guesses on very uneven data. MySQL 8 added histograms too, but you create them explicitly per column and they're not maintained automatically.
Storage layout matters just as much.
InnoDB stores the table clustered on the primary key, and every secondary index includes that primary key. Make the primary key large and every secondary index grows with it, which is why "keep your primary key small and ever-increasing" is such common MySQL advice.
Postgres separates heap storage from indexes, so that trade-off largely disappears. On the other hand, index-only scans depend on the visibility map being up to date, which is one reason VACUUM matters so much.
These days I don't really try to write queries that are "good on both." I write the query, then look at the execution plan on the engine it's actually going to run on. That's the only thing that tells you whether the optimizer agrees with you.
I usually inspect plans in dbForge Studio for PostgreSQL because I like seeing them as a tree and comparing runs side by side. From how I see it, any tool that lets you explore execution plans properly will get you to the same answers.
Has anyone got a query where the Postgres planner made a decision that was clearly worse than MySQL's? I'd genuinely like to see one.
