Introduction

In Chapter 7, Analytics Using Complex Data Types, you learned the necessary skills to effectively analyze data within a SQL database. In this chapter, you will turn your attention to the efficiency of this analysis, investigating how you can increase the performance of SQL queries. Efficiency and performance are key components of data analytics. Without considering these factors, physical constraints, such as time and processing power, can significantly affect the outcome of an analysis.

In this chapter, you will first learn the different ways PostgreSQL performs query planning, in which the PostgreSQL database evaluates the SQL statement and underlying physical implementation and decides how to execute this SQL. You will learn the most basic way of retrieving data, which is scanning by sequence. You will then learn the concept of an index and the two most common indexes in PostgreSQL, the B-tree index and the hash index. From there, you will learn how to kill long...