Understanding the concept of execution plans

It is impossible to understand the use of indexes without understanding the concept of execution plans. Whenever you execute a query in PostgreSQL, it generally goes through four central steps, described as follows:

• Parser: PostgreSQL will check the syntax of the statement.

• Rewrite system: PostgreSQL will rewrite the query (for example, rules and views are handled by the rewrite system).

• Optimizer or planner: PostgreSQL will come up with a smart plan to execute the query as efficiently as possible. At this step, the system will decide whether or not to use indexes.

• Executor: Finally, the execution plan is taken by the executor and the result is generated.

Being able to understand and read execution plans is an essential task of every DBA. To extract the plan from the system, all you need to do is use the explain command, shown as follows:

test=# explain SELECT count(*) FROM t_test;
                              QUERY PLAN                     ...