Query plans. Reading, comprehension and acceleration

SQL is a declarative programming language used to create and manipulate objects in relational databases but it doesn't describe how to get the data. The query optimizer (called simply the optimizer or planner) is a built-in database component that determines the most efficient method for a SQL statement to access requested data.
PostgreSQL devises a query plan for each query it receives. We can use the EXPLAIN command to see what query plan the optimizer creates for any query. Plan-reading is an art that requires some experience to master.

In this report, I will talk about:
- EXPLAIN command;
- its various parameters;
- plan structure;
- its different plan nodes and elements;
- and using indexes and other techniques for Query Performance Tuning.


I will demonstrate all my examples on our free demo database for PostgreSQL. The subject field of this database is airline flights across various airports.

As an experienced programmer, Pavel Tolmachev have worked with PostgreSQL, C#, WinForms, WPF, ASP.NET, and Oracle PL/SQL. He have been involved in the implementation of electronic document management systems and have also worked as a teacher, instructing students, administrators, and application programmers.
Currently, he is engaged in educational programs in Postgres Professional, where he write articles, workshops, training courses, make presentations, and more. Since his student days, he have had a keen interest in DBMS, and especially enjoyed studying the workings of the optimizer.