PostgreSQL High Performance Cookbook

Chapter 11. Query Optimization

In this chapter, we will cover the following recipes:

• Using sample data sets
• Timing overhead
• Studying hot and cold cache behavior
• Clearing the cache
• Querying plan node structure
• Generating an explain plan
• Computing basic costs
• Running sequential scans
• Running bitmap heap and index scans
• Aggregate and hash aggregate
• Grouping
• Working with set operations
• Running a CTE scan
• Nesting loops
• Working with merge and hash join
• Working on semi and anti joins

Introduction

When an end user submits an SQL query to a database, in general any database engine does the parsing and then validates the syntax and semantics of the given query. Once the query passes through the parsing levels, it will enter into the optimizer section. This optimizer section ...