Secrets of Firebird Query Performance

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.

 

CONTENTS


**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**