# Lesson 00: Optimizing Queries > Native Markdown source page: Optimizing Queries --- LLMS index: [llms.txt](/en/llms.txt) --- > [!NOTE] > This page is extracted from the official PDF into native Markdown. Slide text, code and speaker notes are kept in reading order; the original PDF and HTML remain the layout references. > **HTML source:** No matching course-page link was captured in the source inventory. > **Archive:** [QPT-handouts-16-en-20251018.zip](https://edu.postgrespro.com/16/QPT-handouts-16-en-20251018.zip) > **PDF pages:** 9 ## Page 1: Optimizing Queries {#source-page-001} ### Copyright © Postgres Professional, 2019–2024 Authors Authors: Egor Rogov, Pavel Luzanov, Ilya Bashtanov Photo by: Oleg Bartunov (Phu monastery, Bhrikuti summit, Nepal) ### Use of course materials Non-commercial use of course materials (presentations, demonstrations) is allowed without restrictions. Commercial use is possible only with the written permission of Postgres Professional. It is prohibited to make changes to the course materials. ### Feedback Please send your feedback, comments and suggestions to: edu@postgrespro.com ### Disclaimer In no event shall Postgres Professional company be liable for any damages or loss, including loss of profits, that arise from direct or indirect, special or incidental use of course materials. Postgres Professional company specifically disclaims any warranties on course materials. Course materials are provided “as is,” and Postgres Professional company has no obligations to provide maintenance, support, updates, enhancements, or modifications. ## Page 2: Administrators {#source-page-002} ![Source slide page 2: Administrators](images/page-002.png){#source-figure-002 caption="Source slide page 2: Administrators"} - DBA2, 4 days ### Speaker notes To attend these courses, you need a basic understanding of the SQL language. Our curriculum doesn't include a dedicated SQL course, but there are many books and other resources available to help you master SQL. We recommend the book "PostgreSQL. SQL basics" by Evgeny Morgunov For administrators, we offer the following courses. The DBA1 course covers PostgreSQL architecture, installation, basic configuration, and server management. The course covers the key administrative tasks. The course provides an overview of access management, backup, and replication. The DBA2 course focuses on determining appropriate configuration parameters based on an understanding of the internals of the server. It also talks about monitoring the server and using feedback for iterative parameter tweaking. The course covers settings related to localization, extension management, and the server update process. The DBA3 covers backups, as well as configuration and usage scenarios of physical and logical replication. It also provides a general overview of how to build scalable high-availability clusters and discusses the related challenges. This QPT course provides an in-depth look at query planning and execution mechanisms, explores instance parameter configuration related to performance, and covers techniques for identifying and optimizing problematic queries. ## Page 3: Developers {#source-page-003} ![Source slide page 3: Developers](images/page-003.png){#source-figure-003 caption="Source slide page 3: Developers"} ### Speaker notes For backend developers, we offer a special range of courses. The basic course is the DEV1 course. It provides general information about the architecture of PostgreSQL and the use of basic database objects; server-side programming in SQL and PL/pgSQL languages using basic data types (including the composite type and arrays), as well as fronted interactions. The DEV2 course examines the features of the server's internal structure that affect the development of application code, and comprehensively discusses extensibility: the ability to extend server mechanisms with your own code, which allows you to use PostgreSQL to solve a wide variety of tasks. The PGPRO course discusses additional features provided by the Postgres Pro Enterprise DBMS. The QPT and PGPRO courses are intended for both developers and administrators. DBA courses can be interesting to developers if they would like to deepen their knowledge of PostgreSQL internals, or if there is no separate administrator role in their project. ## Page 4: About the QPT Course {#source-page-004} **Duration: 3 days** - Background knowledge required - Familiarity with the Unix operating system - Solid SQL skills (familiarity with PL/pgSQL is not required, but helpful) - PostgreSQL as covered in the DBA1 or DEV1 course - Knowledge and skills gained - In-depth understanding of query planning and execution processes - Configuring instance parameters related to performance - Identifying and optimizing problematic queries ## Page 5: Provided Resources {#source-page-005} **Pre-configured virtual machine** - Xubuntu 24.04 Operating System - PostgreSQL 16 with Russian documentation - Demo database "Air Transport" - DBeaver - Course handouts - Student's guide - presentations, demonstrations, practical assignments and assignment keys - (in html and pdf) - Demonstration Database Schema ### Speaker notes If you are taking the course on your own, start with the Student's guide. Among other things, it shows where to download and how to use the virtual machine and other materials. All course materials are available at: Completing practical assignments is very important to gain the basic skills in working with PostgreSQL. Be sure to try to complete the assignments yourself first, and then review the provided keys, even if the assignment did not raise questions. Assignment keys may contain additional information that is not mentioned in presentations and demonstrations. Course handouts (presentations, demonstrations, practical assignments and keys) are available in two formats. html is good for browsing and copying code snippets. pdf is divided into pages and is easy to print. Additional reference materials will help you quickly find the information you need. DBeaver is installed on the virtual machine. Although we use psql throughout the course, you can use this GUI tool if you like. ## Page 6: Course outline and schedule {#source-page-006} **Each training day is approximately 8 academic hours + lunch (1 hour)** - Each topic usually consists of - a presentation with demos: ~25-60 min - practical assignments: ~20-30 min, including a break ## Page 7: Day 1 {#source-page-007} **Demonstration database** - 01. Aviation - Query execution - 02. Planning and execution - Data Access - 03. 03. Access Methods - 04. 04. Parallel Data Access ## Page 8: Day 2 {#source-page-008} **Access Methods (Continued)** - 05. 05. Index Types - Sorting and Grouping - 06. Sorting - 07. 07. Grouping - Join Methods - 08. Nested loop join - 09. Hash join - 10. Merge join ## Page 9: Day 3 {#source-page-009} **Statistics** - 11. Basic statistics - 12. Advanced Statistics - Query performance tuning - 13. Profiling - 14. Materialization - 15. Functions - 16. 16. Configuration Approaches