PostgreSQL Query Optimization: The Ultimate Guide to Building Efficient Queries

Table of Contents . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .4

 About the Authors . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .12

 About the Technical Reviewer . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .13

 Acknowledgments . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .14

 Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .15

 Chapter 1: Why Optimize? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .22

    What Do We Mean by Optimization? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .22

    Why It Is Difficult: Imperative and Declarative . . . . . . . . . . . . . . . . . . . . . . . . . .23

    Optimization Goals . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .26

    Optimizing Processes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .28

        Optimizing OLTP and OLAP . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .29

        Database Design and Performance . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .29

        Application Development and Performance . . . . . . . . . . . . . . . . . . . . . . . . . . . .31

        Other Stages of the Lifecycle . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .31

    PostgreSQL Specifics . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .32

    Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .33

 Chapter 2: Theory: Yes, We Need It! . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .34

    Query Processing Overview . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .34

        Compilation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .34

        Optimization and Execution . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .35

    Relational, Logical, and Physical Operations . . . . . . . . . . . . . . . . . . . . . . . . . . . . .36

        Relational Operations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .36

        Logical Operations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .40

        Queries as Expressions: Thinking in Sets . . . . . . . . . . . . . . . . . . . . . . . . . . .41

        Operations and Algorithms . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .41

    Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .42

 Chapter 3: Even More Theory: Algorithms . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .43

    Algorithm Cost Models . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .43

    Data Access Algorithms . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .44

        Storage Structures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .45

        Full Scan . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .46

        Index-Based Table Access . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .47

        Index-Only Scan . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .48

        Comparing Data Access Algorithms . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .48

    Index Structures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .51

        What Is an Index? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .51

        B-Tree Indexes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .53

        Why Are B-Trees Used So Often? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .55

        Bitmaps . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .55

        Other Kinds of Indexes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .56

    Combining Relations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .57

        Nested Loops . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .57

        Hash-Based Algorithms . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .59

        Sort-Merge Algorithm . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .61

        Comparing Algorithms . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .62

    Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .62

 Chapter 4: Understanding Execution Plans . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .63

    Putting Everything Together: How an Optimizer Builds an Execution Plan . . .63

    Reading Execution Plans . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .64

    Understanding Execution Plans . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .69

        What Is Going On During Optimization? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .69

        Why Are There So Many Execution Plans to Choose From? . . . . . . . . . . . . . .70

        How Are Execution Costs Calculated? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .71

        How Can the Optimizer Be Led Astray? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .74

    Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .75

 Chapter 5: Short Queries and Indexes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .76

    Which Queries Are Considered Short? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .76

    Choosing Selection Criteria . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .78

        Index Selectivity . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .79

        Unique Indexes and Constraints . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .80

    Indexes and Non-equal Conditions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .84

        Indexes and Column Transformations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .84

    Indexes and the like Operator . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .89

    Using Multiple Indexes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .91

    Compound Indexes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .92

        How Do Compound Indexes Work? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .93

        Lower Selectivity . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .95

        Using Indexes for Data Retrieval . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .95

        Covering Indexes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .96

    Excessive Selection Criteria . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .98

    Partial Indexes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .102

    Indexes and Join Order . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .104

    When Are Indexes Not Used . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .107

        Avoiding Index Usage . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .108

        Why Does PostgreSQL Ignore My Index? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .108

    Let PostgreSQL Do Its Job! . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .111

    How to Build the Right Index(es)? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .116

        To Build or Not to Build . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .116

        Which Indexes Are Needed? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .117

        Which Indexes Are Not Needed? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .117

    Indexes and Short Query Scalability . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .118

    Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .119

 Chapter 6: Long Queries and Full Scans . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .120

    Which Queries Are Considered Long? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .120

    Long Queries and Full Scans . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .122

    Long Queries and Hash Joins . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .123

    Long Queries and the Order of Joins . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .124

        What Is a Semi-join? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .124

        Semi-joins and Join Order . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .126

        More on Join Order . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .128

        What Is an Anti-join? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .131

        Semi- and Anti-joins Using the JOIN Operator . . . . . . . . . . . . . . . . . . . . . .132

        When Is It Necessary to Specify Join Order? . . . . . . . . . . . . . . . . . . . . . . .135

    Grouping: Filter First, Group Last . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .137

    Grouping: Group First, Select Last . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .144

    Using SET operations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .147

    Avoiding Multiple Scans . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .151

    Conclusion . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .157

 Chapter 7: Long Queries: Additional Techniques . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .158

    Structuring Queries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .158

    Temporary Tables and CTEs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .159

        Temporary Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .159

        Common Table Expressions (CTEs) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .161

    Views: To Use or Not to Use . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .166

        Why Use Views? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .173

    Materialized Views . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .173

        Creating and Using Materialized Views . . . . . . . . . . . . . . . . . . . . . . . . . . . . .173

        Refreshing Materialized Views . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .175

        Create a Materialized View or Not? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .175

        Do Materialized Views Need to Be Optimized? . . . . . . . . . . . . . . . . . . . . . . .177

        Dependencies . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .178

    Partitioning . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .179

    Parallelism . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .184

    Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .185

 Chapter 8: Optimizing Data Modification . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .186

    What Is DML? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .186

    Two Ways to Optimize Data Modification . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .186

    How Does DML Work? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .187

        Low-Level Input/Output . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .187

        The Impact of Concurrency Control . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .188

    Data Modification and Indexes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .191

    Mass Updates and Frequent Updates . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .192

    Referential Integrity and Triggers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .193

    Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .194

 Chapter 9: Design Matters . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .195

    Design Matters . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .195

    Why Use a Relational Model? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .199

        Types of Databases . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .200

        Entity-Attribute-Value Model . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .200

        Key-Value Model . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .201

        Hierarchical Model . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .202

        Combining the Best of Different Worlds . . . . . . . . . . . . . . . . . . . . . . . . . . . .203

    Flexibility vs. Efficiency and Correctness . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .203

    Must We Normalize? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .205

    Use and Misuse of Surrogate Keys . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .207

    Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .213

 Chapter 10: Application Development and Performance . . . . . . . . . . . . . . . . . . . . . . . . . . .214

    Response Time Matters . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .214

    World Wide Wait . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .215

    Performance Metrics . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .216

    Impedance Mismatch . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .217

    The Road Paved with Good Intentions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .217

        Application Development Patterns . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .218

        “Shopping List Problem” . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .220

        Interfaces . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .222

        Welcome to the World of ORM . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .222

    In Search of a Better Solution . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .224

    Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .227

 Chapter 11: Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .228

    Function Creation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .228

        Internal Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .229

        User-Defined Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .229

        Introducing Procedural Language . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .230

        Dollar Quoting . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .231

        Function Parameters and Function Output: Void Functions . . . . . . . . . . .232

        Function Overloading . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .233

    Function Execution . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .235

    Function Execution Internals . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .237

    Functions and Performance . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .240

        How Using Functions Can Worsen Performance . . . . . . . . . . . . . . . . . . . . . . . .241

        Any Chance Functions Can Improve Performance? . . . . . . . . . . . . . . . . . . . . .243

    Functions and User-Defined Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .243

        User-Defined Data Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .243

        Functions Returning Composite Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .244

    Using Composite Types with Nested Structure . . . . . . . . . . . . . . . . . . . . . . . . . . . . .248

    Functions and Type Dependencies . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .252

    Data Manipulation with Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .253

    Functions and Security . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .255

    What About Business Logic? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .256

    Functions in OLAP Systems . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .257

        Parameterizing . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .257

        No Explicit Dependency on Tables and Views . . . . . . . . . . . . . . . . . . . . . . . .258

        Ability to Execute Dynamic SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .258

    Stored Procedures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .258

        Functions with No Results . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .258

        Functions and Stored Procedures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .259

        Transaction Management . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .259

        Exception Processing . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .260

    Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .261

 Chapter 12: Dynamic SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .262

    What Is Dynamic SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .262

        Why It Works Better in Postgres . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .262

        What About SQL Injection? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .263

    How to Use Dynamic SQL in OLTP Systems . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .263

    How to Use Dynamic SQL in OLAP Systems . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .269

    Using Dynamic SQL for Flexibility . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .273

    Using Dynamic SQL to Aid the Optimizer . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .280

    FDWs and Dynamic SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .283

    Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .284

 Chapter 13: Avoiding the Pitfalls of Object-Relational Mapping . . . . . . . . . . . . . . . .285

    Why Application Developers Like NORM . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .285

    ORM vs. NORM . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .286

    NORM Explained . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .288

    Implementation Details . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .294

    Complex Searches . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .299

    Updates . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .302

        Insert . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .303

        Update . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .304

        Delete . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .306

    Why Not Store JSON?! . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .307

    Performance Gains . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .307

    Working Together with Application Developers . . . . . . . . . . . . . . . . . . . . . . . . . . . .308

    Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .308

 Chapter 14: More Complex Filtering and Search . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .309

    Full Text Search . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .309

    Multidimensional and Spatial Search . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .311

    Generalized Index Types in PostgreSQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .311

        GIST Indexes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .312

        Indexes for Full Text Search . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .312

        Indexing Very Large Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .313

    Indexing JSON and JSONB . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .314

    Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .318

 Chapter 15: Ultimate Optimization Algorithm . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .319

    Major Steps . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .319

    Step-by-Step Guide . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .320

        Step 1: Short or Long? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .320

        Step 2: Short . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .320

            Step 2.1: The Most Restrictive Criteria . . . . . . . . . . . . . . . . . . . . .321

            Step 2.2: Check the Indexes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .321

            Step 2.3: Add an Excessive Selection Criterion, If Applicable .321

            Step 2.4: Constructing the Query . . . . . . . . . . . . . . . . . . . . . . . . . . . .321

        Step 3: Long . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .322

        Step 4: Incremental Updates . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .322

        Step 5: Non-incremental Long Query . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .322

    But Wait, There Is More! . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .323

    Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .324

 Chapter 16: Conclusion . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .325

 Index . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .327

Write optimized queries. This book helps you write queries that perform fast and deliver results on time. You will learn that query optimization is not a dark art practiced by a small, secretive cabal of sorcerers. Any motivated professional can learn to write efficient queries from the get-go and capably optimize existing queries. You will learn to look at the process of writing a query from the database engine’s point of view, and know how to think like the database optimizer.

The book begins with a discussion of what a performant system is and progresses to measuring performance and setting performance goals. It introduces different classes of queries and optimization techniques suitable to each, such as the use of indexes and specific join algorithms. You will learn to read and understand query execution plans along with techniques for influencing those plans for better performance. The book also covers advanced topics such as the use of functions and procedures, dynamic SQL, and generated queries. All of these techniques are then used together to produce performant applications, avoiding the pitfalls of object-relational mappers.

What You Will Learn

• Identify optimization goals in OLTP and OLAP systems
• Read and understand PostgreSQL execution plans
• Distinguish between short queries and long queries
• Choose the right optimization technique for each query type
• Identify indexes that will improve query performance
• Optimize full table scans
• Avoid the pitfalls of object-relational mapping systems
• Optimize the entire application rather than just database queries

Who This Book Is For

IT professionals working in PostgreSQL who want to develop performant and scalable applications, anyone whose job title contains the words “database developer” or “database administrator" or who is a backend developer charged with programming database calls, and system architects involved in the overall design of application systems running against a PostgreSQL database.