In this lesson, you will learn how table bloat—the accumulation of dead tuples within data pages—misleads the PostgreSQL optimizer into choosing sub-optimal execution plans.
The Mechanism of Bloat-Induced Misjudgment
When you perform frequent UPDATE or DELETE operations, PostgreSQL marks rows as "dead" but cannot immediately reclaim the space they occupy until a VACUUM process runs. This leads to bloat: the table requires more data pages than it would if it were tightly packed.
The PostgreSQL planner relies heavily on statistics regarding the number of pages in a table. When a table is severely bloated, the planner sees a high relpages count in pg_class compared to the actual number of live reltuples.
How the Optimizer Shifts Plans
Because the planner assumes a high ratio of pages-to-rows, it often perceives a sequential scan as "cheaper" than an index scan. Consider this scenario:
- The Expectation: An index scan is usually preferred for retrieving a small percentage of rows.
- The Bloated Reality: Because the table is bloated, the planner assumes that reading the table will require traversing a massive number of pages.
- The Degradation: If the planner calculates that the cost of fetching random pages from the heap (via an index) exceeds the cost of a massive, albeit cached, sequential scan of all pages, it will abandon the index.
If you run EXPLAIN and see a Seq Scan on a query that should clearly use an index, check the bloat. You might see a disparity between the number of live rows and the physical size of the table on disk. The optimizer effectively thinks, "Why bother jumping through the index if the table is so spread out that I'll end up reading almost every page anyway?"
This plan degradation is dangerous because it leads to higher I/O pressure and increased memory usage, as the database is forced to scan dead tuples that should have been removed. Keeping tables lean through aggressive autovacuum tuning or VACUUM FULL (in maintenance windows) ensures the planner's cost model remains grounded in reality.
Key Takeaway: Bloat inflates the perceived cost of heap access, causing the optimizer to prefer expensive sequential scans over efficient index lookups.