How PostgreSQL Internally Processes a SELECT Query: From SQL to Rows

When a PostgreSQL query like SELECT * FROM users is submitted, the database runs it through several internal stages before returning results. First, a parser checks syntax and builds a parse tree, then an analyzer verifies that referenced tables and columns actually exist. A rewriter may transform the query, after which the planner evaluates possible execution strategies — such as a full table scan versus an index scan — and picks the least costly one. The executor then carries out the chosen plan, retrieving data via a buffer cache called shared_buffers before falling back to disk if needed. Understanding this pipeline helps developers use tools like EXPLAIN more effectively to diagnose and improve query performance.