1.1.3 Optimizable vs Utility

The asymmetry between optimizable and utility commands in PostgreSQL highlights a fundamental difference in how these two types of SQL statements are processed within the database engine. Understanding this distinction is crucial for effective performance tuning, monitoring, and security practices.

Key Points

1. Optimizable Commands (5 Types):

   • These include SELECT, INSERT, UPDATE, DELETE, and MERGE.
   • They go through a full pipeline: parse analysis, rewriter, planner, and executor.
   • The planner generates an execution plan that can be optimized and cached.

2. Utility Commands (Everything Else):

   • These include DDL commands (ALTER TABLE, CREATE INDEX), DML commands with side effects (TRUNCATE), transaction management commands (COMMIT, ROLLBACK), and others.
   • They bypass the planner, so there is no execution plan generated or cached.
   • The executor processes them directly using a utility handler.

Asymmetry in Execution Pipeline

• Parse Analysis:
  • Optimizable commands are parsed and analyzed by dedicated transform functions that generate an abstract syntax tree (AST).
  • Utility commands are wrapped in a Query shell without further

Read the full article at DEV Community

---

Want to create content about this topic? Use Nemati AI tools to generate articles, social posts, and more.