Skip to main content

Introducing new learning courses and educational videos from Apress. Start watching

  • Book
  • © 2021

PostgreSQL Query Optimization

The Ultimate Guide to Building Efficient Queries

Apress

Authors:

(view affiliations)
  • Covers query tuning, rather than system configuration or hardware

  • Uses the open-source postgres_air database for more than hundred realistic examples

  • Introduces the NORM application design methodology

Buying options

eBook
USD 39.99
Price excludes VAT (USA)
  • ISBN: 978-1-4842-6885-8
  • Instant PDF download
  • Readable on all devices
  • Own it forever
  • Exclusive offer for individuals only
  • Tax calculation will be finalised during checkout
Softcover Book
USD 49.99
Price excludes VAT (USA)

This is a preview of subscription content, access via your institution.

Table of contents (16 chapters)

  1. Front Matter

    Pages i-xxiii
  2. Why Optimize?

    • Henrietta Dombrovskaya, Boris Novikov, Anna Bailliekova
    Pages 1-12
  3. Theory: Yes, We Need It!

    • Henrietta Dombrovskaya, Boris Novikov, Anna Bailliekova
    Pages 13-21
  4. Even More Theory: Algorithms

    • Henrietta Dombrovskaya, Boris Novikov, Anna Bailliekova
    Pages 23-42
  5. Understanding Execution Plans

    • Henrietta Dombrovskaya, Boris Novikov, Anna Bailliekova
    Pages 43-55
  6. Short Queries and Indexes

    • Henrietta Dombrovskaya, Boris Novikov, Anna Bailliekova
    Pages 57-100
  7. Long Queries and Full Scans

    • Henrietta Dombrovskaya, Boris Novikov, Anna Bailliekova
    Pages 101-138
  8. Long Queries: Additional Techniques

    • Henrietta Dombrovskaya, Boris Novikov, Anna Bailliekova
    Pages 139-166
  9. Optimizing Data Modification

    • Henrietta Dombrovskaya, Boris Novikov, Anna Bailliekova
    Pages 167-175
  10. Design Matters

    • Henrietta Dombrovskaya, Boris Novikov, Anna Bailliekova
    Pages 177-195
  11. Application Development and Performance

    • Henrietta Dombrovskaya, Boris Novikov, Anna Bailliekova
    Pages 197-210
  12. Functions

    • Henrietta Dombrovskaya, Boris Novikov, Anna Bailliekova
    Pages 211-244
  13. Dynamic SQL

    • Henrietta Dombrovskaya, Boris Novikov, Anna Bailliekova
    Pages 245-267
  14. Avoiding the Pitfalls of Object-Relational Mapping

    • Henrietta Dombrovskaya, Boris Novikov, Anna Bailliekova
    Pages 269-292
  15. More Complex Filtering and Search

    • Henrietta Dombrovskaya, Boris Novikov, Anna Bailliekova
    Pages 293-302
  16. Ultimate Optimization Algorithm

    • Henrietta Dombrovskaya, Boris Novikov, Anna Bailliekova
    Pages 303-308
  17. Conclusion

    • Henrietta Dombrovskaya, Boris Novikov, Anna Bailliekova
    Pages 309-310
  18. Back Matter

    Pages 311-319

About this book

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.

You will:

  • 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


Keywords

  • Database Performance
  • Query Performance
  • Query Optimization
  • SQL Performance
  • SQL Tuning
  • Optimizing SQL
  • System Tuning
  • Object Relational Mapping (ORM)
  • Execution Plans
  • Join Algorithms
  • Indexing
  • Dynamic SQL
  • Stored Procedures
  • Postgres
  • PostgreSQL
  • PostgreSQL High Performance
  • Learning PostgreSQL

Authors and Affiliations

  • Braviant Holdings, Chicago, USA

    Henrietta Dombrovskaya

  • HSE University, Saint Petersburg, Russia

    Boris Novikov

  • Zendesk, Madison, USA

    Anna Bailliekova

About the authors

Henrietta Dombrovskaya is a database researcher and developer with over 35 years of academic and industrial experience. She holds a PhD in computer science from the University of Saint Petersburg, Russia. At present, she is Associate Director of Databases at Braviant Holdings, Chicago, Illinois. She is an active member of the PostgreSQL community, a frequent speaker at the PostgreSQL conference, and a local organizer of the Chicago PostgreSQL User Group. Her research interests are tightly coupled with practice and are focused on developing efficient interactions between applications and databases. She is a winner of the “Technologist of the Year” 2019 award of the Illinois Technology Association.

Boris Novikov is currently a professor in the Department of Informatics at National Research University Higher School of Economics in Saint Petersburg, Russia. He graduated from Leningrad University’s School of Mathematics and Mechanics. He has worked for Saint Petersburg University for a number of years and moved to his current position in January, 2019. His research interests are in a broad area of information management and include several aspects of design, development, and tuning of databases, applications, and database management systems. He also has interests in distributed scalable systems for stream processing and analytics.

Anna Bailliekova is Senior Data Engineer at Zendesk. Previously, she built ETL pipelines, data warehouse resources, and reporting tools as a team lead on the Division Operations team at Epic. She has also held analyst roles on a variety of political campaigns and at Greenberg Quinlan Rosner Research. She received her undergraduate degree cum laude with College Honors in political science and computer science from Knox College in Galesburg, Illinois.

Bibliographic Information

  • Book Title: PostgreSQL Query Optimization

  • Book Subtitle: The Ultimate Guide to Building Efficient Queries

  • Authors: Henrietta Dombrovskaya, Boris Novikov, Anna Bailliekova

  • DOI: https://doi.org/10.1007/978-1-4842-6885-8

  • Publisher: Apress Berkeley, CA

  • eBook Packages: Professional and Applied Computing, Professional and Applied Computing (R0), Apress Access Books

  • Copyright Information: Henrietta Dombrovskaya, Boris Novikov, Anna Bailliekova 2021

  • Softcover ISBN: 978-1-4842-6884-1

  • eBook ISBN: 978-1-4842-6885-8

  • Edition Number: 1

  • Number of Pages: XXIII, 319

  • Number of Illustrations: 116 b/w illustrations

  • Topics: Database Management, Open Source

Buying options

eBook
USD 39.99
Price excludes VAT (USA)
  • ISBN: 978-1-4842-6885-8
  • Instant PDF download
  • Readable on all devices
  • Own it forever
  • Exclusive offer for individuals only
  • Tax calculation will be finalised during checkout
Softcover Book
USD 49.99
Price excludes VAT (USA)