Purchases through FirebirdSQL shop generate commissions that fund open source Firebird development.

100% of profit from this item goes to Firebird Foundation to finance development of Firebird database.

Book: Secrets of Firebird SQL Optimizer

This book is authored by Denis Simonov, who has contributed to numerous sections of the official Firebird SQL documentation.

"Secrets of Firebird SQL Optimizer" is currently available for pre-order in the following languages: English, Portuguese, German, Russian.

Would like to have book in your language? Send inquiry to [email protected], and if there will be many requests, we will prepare the translation.

The book is distributed in PDF format (421 pages), with each language version available separately.

What makes this book an ideal gift for any Firebird developer? Two reasons: 1) it's the first comprehensive resource offering deep insights into how Firebird queries work internally, combining both theoretical concepts and practical examples, and 2) 100% of the profits from book sales are donated to the Firebird Foundation as commission, directly supporting Firebird development.

Download sample chapters (Introduction, "Chapter 1. How to Measure Query Performance?", and "Chapter 3. Data Access Methods Used in Firebird").

CONTENTS

Introduction: The Path to Understanding the Firebird Optimizer
Why Performance Matters
The Evolution of Optimization Approaches: From Hardware to Understanding
The Trap of Endless Resource Scaling
Typical Mistakes: When Good Code Becomes Slow
Evolution of Firebird and the Optimizer: From Firebird 1 to 5
What This Book Is About: A Roadmap by Chapters
How to Read This Book: Practical Recommendations
What You Will Get After Reading This Book

Chapter 1. How to Measure Query Performance?
1.1. Enabling Execution Statistics Output in isql
1.2. What Do These Numbers Mean?
1.3. How to Get the Time to Fetch All Records
1.4. Query Transformation
1.5. Measuring Query Performance Under Load
1.6. Using Tracing to Measure Query Performance
1.7. Conclusions

Chapter 2. What is a query execution plan?
2.1. Legacy plan
2.2. Explain plan
2.3. How to get a query execution plan?
2.3.1. Getting a query execution plan in Firebird API
Getting a query execution plan in isql
2.3.2. Getting a query execution plan in a trace
2.4. Obtaining Query Execution Plans in MON$ Tables
2.4.1. MON$STATEMENTS Table
2.4.2. MON$COMPILED_STATEMENTS Table
2.5. A Look into the Future
2.5.1. Getting the Plan with RDB$SQL.EXPLAIN
2.6. Conclusion

Chapter 3. Data Access Methods Used in Firebird
3.1. Terminology
3.2. Primary Data Access Methods
3.2.1. Reading a Table
Full Table Scan
Access by Record Identifier
Positioned Access
3.2.2. Index Access
Index Selectivity
Partial Indexes
Bitmaps
Range Scan
Bitmap intersection and union
Index navigation
3.2.3. Access via RDB$DB_KEY
3.2.4. External table (External table scan)
3.2.5. Virtual table (Virtual table scan)
3.2.6. Local temporary table (Local table)
3.2.7. Procedural access
3.3. Filters
3.3.1. Predicate Checking
Invariant Predicate Checking
3.3.2. Sorting
Refetch
3.3.3. Aggregation
Filtering in the HAVING Clause
3.3.4. Counters
3.3.5. Singularity Check
3.3.6. Record Locking
3.3.7. Conditional Stream Branching
3.3.8. Record Buffering
3.3.9. Sliding Window (Window)
3.4. Merging Methods
3.4.1. Joins
Nested Loop Join
Hash Join
Single-Pass Merge (Merge)
Full Outer Join
Join with a stored procedure
Join with table expressions
Joining with Views
3.4.2. Unions (Union)
Materialization of Non-Deterministic Expressions
Materialization of Subqueries
3.4.3. Recursion
3.5. Optimization Strategies
3.6. Conclusion

Chapter 4. Query Transformation
4.1. Filtering Predicate Transformation
4.1.1. LIKE Predicate Transformation
4.1.2. SIMILAR TO Predicate Transformation
4.1.3. Predicate Inversion
4.1.4. Transformation of the IN Predicate with a Value List
4.2. Subquery Transformation
4.2.1. Transformation of IN to SOME/ANY
4.2.2. Transformation of ANY/ALL Subqueries into a Correlated Form
4.2.3. Transformation of NOT IN, NOT ANY, ALL <>
4.2.4. Transformation to semi-join
4.2.5. Transformation to anti-join
4.3. Transformation of joins (JOINs)
4.3.1. Transformation of RIGHT JOIN to LEFT JOIN
4.3.2. Transformation of OUTER JOIN to INNER JOIN
4.3.3. Transformation of OUTER JOIN to anti-join
4.4. Conclusion

Chapter 5. Per-table Statistics
5.1. Obtaining Per-table Statistics in isql
5.2. Getting Per-Table Statistics Using Tracing
5.3. Getting Per-Table Statistics Using Monitoring Tables
5.3.1. MON$TABLE_STATS
5.3.2. MON$RECORD_STATS
5.3.3. Examples of Getting Per-Table Statistics Using MON$ Tables
5.4. Conclusion

Chapter 6. Getting Statistics with gstat
6.1. Description of the gstat utility
6.2. Getting Statistics Using fbsvcmgr
6.2.1. fbsvcmgr Parameter Syntax
SPB Syntax
6.2.2. Getting Help
6.2.3. Service Connection Parameters
6.2.4. Using Services with a Non-Default Security Database
6.2.5. Parameters for Getting Statistics with fbsvcmgr
6.2.6. Example of Statistics Analysis for Tables and Indexes from a Query
6.3. Analysis of the Obtained Statistics
6.3.1. Header Page Statistics
6.3.2. Table Statistics
Primary and Secondary Pages
Record Versions and Fragments
6.3.3. Table Index Statistics
Index Depth
Number of Leaf Pages
Number of Nodes and Key Duplicates
Partial Indexes
Key Size and Compression Ratio
Clustering Factor

Chapter 7. Indexes
7.1. Types of Indexes
7.2. When Can the Optimizer Use an Index?
7.3. Using Indexes for Filtering
7.3.1. BETWEEN Operator
7.3.2. IN Predicate
7.3.3. Index for Boolean Columns or Expressions
7.3.4. Index on Expression
7.3.5. Disabling Index Usage
7.3.6. Index Statistics
Index Selectivity
Selectivity of Composite Index Segments
Selectivity of Indexed Predicates
7.3.7. Composite Indexes
7.3.8. Using Multiple Indexes
7.3.9. Conditional Stream Branching
7.3.10. Composite Index or Several Simple Indexes?
Composite Index Selectivity is More Accurate
Composite Indexes are More Expensive to Maintain
Composite Indexes Cannot Be Used for Combining OR Predicates
It is Impossible to Specify an Expression for One of the Columns Included in the Composite Index
When Should You Create a Composite Index?
7.3.11. Partial Indexes
Unique Partial Indexes
When Can Partial Indexes Be Used by the Optimizer?
Selectivity of a Partial Index
Reducing Index Size
Using Partial Indexes with Non-Selective Predicates
Partial Indexes with Conditions Not Including the Key Column
Partial Indexes with Conditions Combined via OR
Partial Indexes with the IN Predicate in the Filter Condition
When Partial Indexes Cannot Be Used
7.4. Using Indexes in Join Conditions
7.4.1. How are indexes used in join conditions?
7.4.2. Using indexes with the Nested loop join algorithm
Using Multiple Indexes
Using Composite Indexes
7.4.3. Using Index Statistics by the Hash Join Algorithm
7.5. Using Index for Sorting
7.5.1. When can an index be used for sorting?
7.5.2. Navigation (sorting) by a simple index
7.5.3. Navigation (sorting) using an index on an expression
7.5.4. Disabling Index Navigation
7.5.5. Navigation (Sorting) by Composite Index
7.5.6. Filtering During Navigation (Sorting) by Index
Non-indexed Filter Predicate
Filtering by a Different Index
Filtering by the Same Index Used for Navigation
Filtering by a Segment of the Same Composite Index Used for Navigation
7.5.7. Limiting the Number of Rows
Skipping N Records
Counters and Filters
7.5.8. Index Navigation (Sorting) in Multi-Table Queries
Limiting the Number of Records
7.5.9. Index Navigation (Sorting) Cost
7.6. Using Index for Grouping
7.6.1. Filtering in Grouping
Non-indexed Filter Predicate
Filtering by a Different Index
Filtering by the Same Index Used for Grouping
Filtering a Segment of the Same Composite Index Used for Grouping
7.6.2. Grouping and FIRST/ROWS/OFFSET Limiters
7.6.3. Grouping and Sorting
7.6.4. Grouping in Multi-Table Queries
7.7. Using Indexes for MIN/MAX Aggregate Function Calculation
7.7.1. MIN/MAX and NULL
7.7.2. Disabling Index Usage in MIN/MAX Calculation
7.7.3. MIN/MAX Calculation and Filtering
7.7.4. Non-indexed Filter Predicate
7.7.5. Indexed Filter Predicate Using Another Index
7.7.6. Indexed Filter Predicate Using the Same Index
7.7.7. Indexed Filter Predicate on a Segment of the Same Index
7.7.8. Calculating MIN/MAX with Grouping
7.7.9. Calculating MIN/MAX in queries with table joins
7.8. Conclusion

Chapter 8. Optimizing Joins
8.1. Joining Two Tables
8.1.1. INNER JOIN of Two Tables
Indexes for Fields and Expressions
Recalculating Index Statistics
Hints +0 or ||'' for the Optimizer
Using LEFT JOIN as a Hint
Using an Explicitly Specified Plan
Additional Filter Conditions
8.1.2. LEFT/RIGHT JOIN of Two Tables
Additional Filter Conditions
What if you need to avoid outer join transformations
8.1.3. FULL JOIN of Two Tables
Additional Filter Conditions
8.2. Table Joins with Stored Procedures
8.2.1. Inner Join of a Stored Procedure and a Table (No Dependencies via Input Parameters)
8.2.2. One-Sided Outer Joins of Stored Procedure and Table
8.2.3. Joins of a Procedure Dependent on the Data Stream via Input Parameters
8.3. Joining a Table with a Table Expression
8.3.1. Joins with Simple Table Expressions
8.3.2. Inner Joins with Complex Table Expressions
8.3.3. One-sided Outer Joins with Complex Table Expressions
8.3.4. LATERAL Derived Table Joins
Replacing Similar Subqueries
Eliminating Query Part Duplication in UNION ALL
Using LATERAL to Specify Join Order with Derived Tables
8.4. Joins of Three or More Tables
8.4.1. Specifying Order and/or Algorithm for Inner Joins of Three Tables
8.4.2. Inner and Outer Joins in a Single Query
8.5. Conclusion

Chapter 9. Optimization of Sorts
9.1. Estimating the Cost of an External Sort
9.2. Optimizing Wide External Sorts (Refetch)
9.2.1. What configuration parameter values to choose?
9.3. Query transformation to reduce sort width
9.4. Conclusions

Chapter 10. Optimizing Grouping Operations
10.1. Efficiency of Grouping Using External Sorting
10.2. Using Additional Aggregate Functions to Reduce Sort Width in Aggregation
10.3. Joining Other Tables After Grouping
10.4. GROUP BY or DISTINCT
10.5. Conclusions

Chapter 11. Conclusion
11.1. Feedback