PostgreSQL vs Oracle: When Higher Cost Means Better Performance
Database optimizers don’t see the future. They make decisions based on statistics, estimates, and cost models — and most of the time, those decisions work remarkably well. But what if the optimizer says: “The Sequential Scan is cheaper.” …and reality says: “The Index Only Scan is almost twice as fast.” That is exactly what I wanted to investigate. For this experiment, I created a deliberately skewed PostgreSQL dataset containing 100 million rows , where almost every row has the same customer_type and only a single row represents a rare value. This creates the perfect environment to challenge the optimizer’s assumptions and compare estimated cost with actual execution time . The question is simple: Does PostgreSQL’s estimated cost really tell us which execution plan will be faster? The results were surprising. Not always. And the difference between what PostgreSQL estimated and what actually happened reveals an important lesson about reading and troubleshooting execution plan...