Muutke küpsiste eelistusi

E-raamat: PostgreSQL Query Optimization: The Ultimate Guide to Building Efficient Queries

4.15/5 (58 hinnangut Goodreads-ist)
, ,
  • Formaat: EPUB+DRM
  • Ilmumisaeg: 22-Apr-2021
  • Kirjastus: APress
  • Keel: eng
  • ISBN-13: 9781484268858
Teised raamatud teemal:
  • Formaat - EPUB+DRM
  • Hind: 67,91 €*
  • * hind on lõplik, st. muud allahindlused enam ei rakendu
  • Lisa ostukorvi
  • Lisa soovinimekirja
  • See e-raamat on mõeldud ainult isiklikuks kasutamiseks. E-raamatuid ei saa tagastada.
PostgreSQL Query Optimization: The Ultimate Guide to Building Efficient QueriesSuurem pilt
  • Formaat: EPUB+DRM
  • Ilmumisaeg: 22-Apr-2021
  • Kirjastus: APress
  • Keel: eng
  • ISBN-13: 9781484268858
Teised raamatud teemal:

DRM piirangud

  • Kopeerimine (copy/paste):

    ei ole lubatud

  • Printimine:

    ei ole lubatud

  • Kasutamine:

    Digitaalõiguste kaitse (DRM)
    Kirjastus on väljastanud selle e-raamatu krüpteeritud kujul, mis tähendab, et selle lugemiseks peate installeerima spetsiaalse tarkvara. Samuti peate looma endale  Adobe ID Rohkem infot siin. E-raamatut saab lugeda 1 kasutaja ning alla laadida kuni 6'de seadmesse (kõik autoriseeritud sama Adobe ID-ga).

    Vajalik tarkvara
    Mobiilsetes seadmetes (telefon või tahvelarvuti) lugemiseks peate installeerima selle tasuta rakenduse: PocketBook Reader (iOS / Android)

    PC või Mac seadmes lugemiseks peate installima Adobe Digital Editionsi (Seeon tasuta rakendus spetsiaalselt e-raamatute lugemiseks. Seda ei tohi segamini ajada Adober Reader'iga, mis tõenäoliselt on juba teie arvutisse installeeritud )

    Seda e-raamatut ei saa lugeda Amazon Kindle's. 

Intermediate-Advanced user level

Write optimized queries. This book helps you write queries that perform fast and deliver results on time. You will see that query optimization is not a magic art available only to a limited circle of wizards. Any motivated professional can learn to write efficient queries and become competent in optimizing them. You will learn to look at the process of writing a query from the database engine’s point of view, and how to “think” like a database optimizer.

The book starts with a discussion of what is a performant system and progresses to measuring performance and setting performance goals. You will be introduced to different classes of queries and to suitable optimization techniques 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 covers the use of object-relational mapper systems by explaining their pitfalls and showing you how to avoid those pitfalls. The book also covers advanced topics such as the use of functions and procedures, dynamic SQL, and generated queries.


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

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
About the Authors xi
About the Technical Reviewer xiii
Acknowledgments xv
Introduction xvii
Chapter 1 Why Optimize?
1(12)
What Do We Mean by Optimization?
1(1)
Why It Is Difficult: Imperative and Declarative
2(3)
Optimization Goals
5(2)
Optimizing Processes
7(1)
Optimizing OLTP and OLAP
8(1)
Database Design and Performance
8(2)
Application Development and Performance
10(1)
Other Stages of the Lifecycle
10(1)
PostgreSQL Specifics
11(1)
Summary
12(1)
Chapter 2 Theory: Yes, We Need It!
13(10)
Query Processing Overview
13(1)
Compilation
13(1)
Optimization and Execution
14(1)
Relational, Logical, and Physical Operations
15(1)
Relational Operations
15(4)
Logical Operations
19(1)
Queries as Expressions: Thinking in Sets
20(1)
Operations and Algorithms
20(1)
Summary
21(2)
Chapter 3 Even More Theory: Algorithms
23(20)
Algorithm Cost Models
23(1)
Data Access Algorithms
24(1)
Storage Structures
25(1)
Full Scan
26(1)
Index-Based Table Access
27(1)
Index-Only Scan
28(1)
Comparing Data Access Algorithms
28(3)
Index Structures
31(1)
What Is an Index?
31(2)
B-Tree Indexes
33(2)
Why Are B-Trees Used So Often?
35(1)
Bitmaps
35(1)
Other Kinds of Indexes
36(1)
Combining Relations
37(1)
Nested Loops
37(2)
Hash-Based Algorithms
39(2)
Sort-Merge Algorithm
41(1)
Comparing Algorithms
42(1)
Summary
42(1)
Chapter 4 Understanding Execution Plans
43(14)
Putting Everything Together: How an Optimizer Builds an Execution Plan
43(1)
Reading Execution Plans
44(5)
Understanding Execution Plans
49(1)
What Is Going On During Optimization?
49(1)
Why Are There So Many Execution Plans to Choose From?
50(1)
How Are Execution Costs Calculated?
51(3)
How Can the Optimizer Be Led Astray?
54(1)
Summary
55(2)
Chapter 5 Short Queries and Indexes
57(44)
Which Queries Are Considered Short?
57(2)
Choosing Selection Criteria
59(1)
Index Selectivity
60(1)
Unique Indexes and Constraints
61(4)
Indexes and Non-equal Conditions
65(1)
Indexes and Column Transformations
65(5)
Indexes and the like Operator
70(2)
Using Multiple Indexes
72(1)
Compound Indexes
73(1)
How Do Compound Indexes Work?
74(2)
Lower Selectivity
76(1)
Using Indexes for Data Retrieval
76(1)
Covering Indexes
77(2)
Excessive Selection Criteria
79(4)
Partial Indexes
83(2)
Indexes and Join Order
85(3)
When Are Indexes Not Used
88(1)
Avoiding Index Usage
89(1)
Why Does PostgreSQL Ignore My Index?
89(3)
Let PostgreSQL Do Its Job!
92(5)
How to Build the Right Index(es)?
97(1)
To Build or Not to Build
97(1)
Which Indexes Are Needed?
98(1)
Which Indexes Are Not Needed?
98(1)
Indexes and Short Query Scalability
99(1)
Summary
100(1)
Chapter 6 Long Queries and Full Scans
101(38)
Which Queries Are Considered Long?
101(2)
Long Queries and Full Scans
103(1)
Long Queries and Hash Joins
104(1)
Long Queries and the Order of Joins
105(1)
What Is a Semi-join?
105(2)
Semi-joins and Join Order
107(2)
More on Join Order
109(3)
What Is an Anti-join?
112(1)
Semi- and Anti-joins Using the JOIN Operator
113(3)
When Is It Necessary to Specify Join Order?
116(2)
Grouping: Filter First, Group Last
118(7)
Grouping: Group First, Select Last
125(3)
Using SET operations
128(4)
Avoiding Multiple Scans
132(6)
Conclusion
138(1)
Chapter 7 Long Queries: Additional Techniques
139(28)
Structuring Queries
139(1)
Temporary Tables and CTEs
140(1)
Temporary Tables
140(2)
Common Table Expressions (CTEs)
142(5)
Views: To Use or Not to Use
147(7)
Why Use Views?
154(1)
Materialized Views
154(1)
Creating and Using Materialized Views
154(2)
Refreshing Materialized Views
156(1)
Create a Materialized View or Not?
156(2)
Do Materialized Views Need to Be Optimized?
158(1)
Dependencies
159(1)
Partitioning
160(5)
Parallelism
165(1)
Summary
166(1)
Chapter 8 Optimizing Data Modification
167(10)
What Is DML?
167(1)
Two Ways to Optimize Data Modification
167(1)
How Does DML Work?
168(1)
Low-Level Input/Output
168(1)
The Impact of Concurrency Control
169(3)
Data Modification and Indexes
172(1)
Mass Updates and Frequent Updates
173(1)
Referential Integrity and Triggers
174(1)
Summary
175(2)
Chapter 9 Design Matters
177(20)
Design Matters
177(4)
Why Use a Relational Model?
181(1)
Types of Databases
182(1)
Entity-Attribute-Value Model
182(1)
Key-Value Model
183(1)
Hierarchical Model
184(1)
Combining the Best of Different Worlds
185(1)
Flexibility vs. Efficiency and Correctness
185(2)
Must We Normalize?
187(2)
Use and Misuse of Surrogate Keys
189(6)
Summary
195(2)
Chapter 10 Application Development and Performance
197(14)
Response Time Matters
197(1)
World Wide Wait
198(1)
Performance Metrics
199(1)
Impedance Mismatch
200(1)
The Road Paved with Good Intentions
200(1)
Application Development Patterns
201(2)
"Shopping List Problem"
203(2)
Interfaces
205(1)
Welcome to the World of ORM
205(2)
In Search of a Better Solution
207(3)
Summary
210(1)
Chapter 11 Functions
211(34)
Function Creation
211(1)
Internal Functions
212(1)
User-Defined Functions
212(1)
Introducing Procedural Language
213(1)
Dollar Quoting
214(1)
Function Parameters and Function Output: Void Functions
215(1)
Function Overloading
216(2)
Function Execution
218(2)
Function Execution Internals
220(3)
Functions and Performance
223(1)
How Using Functions Can Worsen Performance
224(2)
Any Chance Functions Can Improve Performance?
226(1)
Functions and User-Defined Types
226(1)
User-Defined Data Types
226(1)
Functions Returning Composite Types
227(4)
Using Composite Types with Nested Structure
231(4)
Functions and Type Dependencies
235(1)
Data Manipulation with Functions
236(2)
Functions and Security
238(1)
What About Business Logic?
239(1)
Functions in OLAP Systems
240(1)
Parameterizing
240(1)
No Explicit Dependency on Tables and Views
241(1)
Ability to Execute Dynamic SQL
241(1)
Stored Procedures
241(1)
Functions with No Results
241(1)
Functions and Stored Procedures
242(1)
Transaction Management
242(1)
Exception Processing
243(1)
Summary
244(1)
Chapter 12 Dynamic SQL
245(24)
What Is Dynamic SQL
245(1)
Why It Works Better in Postgres
245(1)
What About SQL Injection?
246(1)
How to Use Dynamic SQL in OLTP Systems
246(6)
How to Use Dynamic SQL in OLAP Systems
252(4)
Using Dynamic SQL for Flexibility
256(7)
Using Dynamic SQL to Aid the Optimizer
263(3)
FDWs and Dynamic SQL
266(1)
Summary
267(2)
Chapter 13 Avoiding the Pitfalls of Object-Relational Mapping
269(24)
Why Application Developers Like NORM
269(1)
ORM vs. NORM
270(2)
NORM Explained
272(6)
Implementation Details
278(5)
Complex Searches
283(3)
Updates
286(1)
Insert
287(1)
Update
288(2)
Delete
290(1)
Why Not Store JSON?!
291(1)
Performance Gains
291(1)
Working Together with Application Developers
292(1)
Summary
292(1)
Chapter 14 More Complex Filtering and Search
293(10)
Full Text Search
293(2)
Multidimensional and Spatial Search
295(1)
Generalized Index Types in PostgreSQL
295(1)
GIST Indexes
296(1)
Indexes for Full Text Search
296(1)
Indexing Very Large Tables
297(1)
Indexing JSON and JSONB
298(4)
Summary
302(1)
Chapter 15 Ultimate Optimization Algorithm
303(6)
Major Steps
303(1)
Step-by-Step Guide
304(1)
Step 1 Short or Long?
304(1)
Step 2 Short
304(2)
Step 3 Long
306(1)
Step 4 Incremental Updates
306(1)
Step 5 Non-incremental Long Query
306(1)
But Wait, There Is More!
307(1)
Summary
308(1)
Chapter 16 Conclusion
309(2)
Index 311
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 Universitys 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.
Lisainfo e-raamatute kohta Lisainfo e-raamatute kohta
Püsilink: https://www.kriso.ee/db/97814842688586e.html