Upcoming book from the author of many parts of Firebird SQL official documentation: Denis Simonov.
The book "Secrets of Firebird Query Performance" will be available in English, German, Portuguese, and Russian.
Planned publication date: November 12, 2025, 320 pages in A4 format (printable PDF).
Subscribe to Firebird Substack to do not miss it and receive special discount.
**Preface**
**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 for retrieving 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 query execution plan in Firebird API
Getting query execution plan in isql
2.3.2. Getting query execution plan in tracing
2.4. Getting query execution plan in MON$ tables
2.4.1. MON$STATEMENTS table
2.4.2. MON$COMPILED_STATEMENTS table
2.5. Looking into the future
2.5.1. Getting plan using RDB$SQL.EXPLAIN
2.6. Conclusion
**Chapter 3. Access methods used in Firebird**
3.1. Terminology
3.2. Primary access methods
3.2.1. Table reading
Full table scan
Access via record identifier
Positioned access
3.2.2. Index access
Index selectivity
Partial indexes
Bitmaps
Range scanning
Intersection and union of bit masks
Index navigation
3.2.3. Access via RDB$DB_KEY
3.2.4. External table scan
3.2.5. Virtual table scan
3.2.6. Local temporary 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
HAVING clause filtering
3.3.4. Counters
3.3.5. Singularity checking
3.3.6. Record locking
3.3.7. Conditional stream branching
3.3.8. Record buffering
3.3.9. Sliding window
3.4. Merge methods
3.4.1. Joins
Nested loop join
Hash join
Single-pass merge
3.4.2. Unions
Materialization of non-deterministic expressions
Subquery materialization
3.4.3. Recursion
3.5. Optimization strategies
3.6. Conclusion
**Chapter 4. Query transformation**
4.1. Filter predicate transformation
4.1.1. LIKE predicate transformation
4.1.2. SIMILAR TO predicate transformation
4.1.3. Predicate inversion
4.1.4. IN predicate transformation with value lists
4.2. Subquery transformation
4.2.1. IN to SOME/ANY transformation
4.2.2. Converting ANY/ALL subqueries to correlated form
4.2.3. NOT IN, NOT ANY, ALL <> transformation
4.2.4. Semi-join transformation
4.2.5. Anti-join transformation
4.3. JOIN transformation
4.3.1. RIGHT JOIN to LEFT JOIN transformation
4.3.2. OUTER JOIN to INNER JOIN transformation
4.3.3. OUTER JOIN to anti-join transformation
4.4. Conclusion
**Chapter 5. Per-table statistics**
5.1. Getting 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 with MON$ tables
5.4. Conclusion
**Chapter 6. Getting statistics with gstat**
6.1. Description of gstat utility
6.2. Getting statistics with 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 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 query
6.3. Analyzing 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. Expression-based index
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 multiple simple indexes?
Filtering by composite index is cheaper
Composite index selectivity is more accurate
Composite indexes are more expensive to maintain
Composite indexes cannot be used for OR predicate unions
Cannot specify expression for one column in 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?
Partial index selectivity
Reducing index size
Using partial indexes with non-selective predicates
Partial indexes with conditions not including key column
Partial indexes with OR-joined conditions
Partial indexes with IN predicate in filter condition
When partial indexes cannot be used
7.4. Using indexes in table join conditions
7.4.1. How are indexes used in join link conditions?
7.4.2. Using indexes by nested loop join algorithm
Using multiple indexes
Using composite indexes
7.4.3. Using index statistics by 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 simple index
7.5.3. Navigation (sorting) using expression-based index
7.5.4. Disabling index navigation
7.5.5. Navigation (sorting) by composite index
7.5.6. Filtering during index navigation (sorting)
Non-indexed filter predicate
Filtering by another index
Filtering by the same index used for navigation
Filtering by segment of the same composite index used for navigation
7.5.7. Limiting number of rows
Skipping N records
Counters and filters
7.5.8. Index navigation (sorting) in multi-table queries
Record count limitation
7.5.9. Cost of index navigation (sorting)
7.6. Using index for grouping
7.6.1. Filtering in grouping
Non-indexed filter predicate
Filtering by another index
Filtering by the same index used for grouping
Filtering by 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 index for computing MIN/MAX aggregate functions
7.7.1. MIN/MAX and NULL
7.7.2. Disabling index when computing MIN/MAX
7.7.3. MIN/MAX computation and filtering
7.7.4. Non-indexed filter predicate
7.7.5. Indexed filter predicate by another index
7.7.6. Indexed filter predicate by the same index
7.7.7. Indexed filter predicate by segment of the same index
7.7.8. MIN/MAX computation with grouping
7.7.9. MIN/MAX computation in queries with table joins
7.8. Conclusion
**8. Chapter 8. JOIN optimization**
8.1. INNER and OUTER
8.2. Order of tables and logic of optimizer
8.3 Frequent errors
8.4. HASH join
8.5. Rare situations
8.6. Conclusion
**Chapter 9. Sorting optimization**
9.1. Sorting: GROUP BY, ORDER BY, DISTINCT
9.2. Who Firebird does sorting
9.3. REFETCH
9.4. Optimization approach for sorting
9.5. Effect of OPTIMIZE FOR
9.6. Examples
**Chapter 10. Grouping optimization**
10.1. How GROUP BY works on low level
10.2. Difference between GROUP BY for single and several tables
10.3. Examples and best practices
10.4. Conclusion
**Chapter 11. Tricks using LATERAL**
**Chapter 12. Procedural code optimization**
**Chapter 13. Using profiler**
**Chapter 14. Finding bottlenecks**
**Chapter 15. Conclusion**