SQL query optimization involves the DBMS determining the most efficient plan to execute a query, with the query optimizer responsible for evaluating different execution plans based on cost. The Plan Explorer tool, implemented for PostgreSQL, visualizes these plans and provides insights into the optimizer's decisions by generating various diagrams. The tool can operate in both standalone and server modes, enabling deeper analysis of query execution and costs.