D. Yemanov

A PLAN clause optionally allows you to provide your own instructions to the engine and have it ignore the plan supplied by the optimizer. Firebird 2 enhancements allow you to specify more possible paths for the engine.

For example:

PLAN (A ORDER IDX1 INDEX (IDX2, IDX3))

For more details, please refer to the topic in the DML section, Query plans, Improvements in handling user-specified query plans.

This chapter represents a collection of changes done in Firebird 2.0 to optimize many aspects of performance.

The following changes affect all databases.

O. Loa, D. Yemanov

• Much faster algorithms to process the dirty pages tree.

Firebird 2.0 offers a more efficient processing of the list of modified pages, a.k.a. the dirty pages tree. It affects all kinds of batch data modifications performed in a single transaction and eliminates the known issues with performance getting slower when using a buffer cache of >10K pages. This change also improves the overall performance of data modifications.

• Increased maximum page cache size to 128K pages (2GB for 16K page size).

O. Loa

Constant IN predicate or multiple OR Booleans are now evaluated faster.

Sparse bitmap operations were optimized to handle multiple OR Booleans or an IN (<constant list>) predicate more efficiently, improving performance of these operations.

A. Brinkman

The optimizer will now use a more realistic cost value for unique retrieval.

D. Yemanov

NOT conditions are simplified and optimized via an index when possible.

Example

(NOT NOT A = 0) → (A = 0) (NOT A > 0) → (A ⇐ 0)

If a HAVING clause or any outer-level SELECT refers to a field being grouped by, this conjunct is distributed deeper in the execution path than the grouping, thus allowing an index scan to be used. In other words, it allows the HAVING clause not only be treated as the WHERE clause in this case, but also be optimized the same way.

Examples

select rdb$relation_id, count(*)
from rdb$relations
group by rdb$relation_id
having rdb$relation_id > 10

select * from (
   select rdb$relation_id, count(*)
   from rdb$relations
   group by rdb$relation_id
   ) as grp (id, cnt)
where grp.id > 10

In both cases, an index scan is performed instead of a full scan.

Distribute UNION conjunctions to the inner streams when possible.

Improved CROSS JOIN and merge/sort handling.

Let's choose a reasonable join order for intermixed inner and outer joins.

MERGE PLAN may now be generated for joins using equality comparison on expressions.

back to top of page

This group of optimizations affects databases that were created under Firebird 2.

See Selectivity maintenance per segment in the Indexing chapter.

Previously, IS NULL and STARTING WITH predicates were optimized separately from others, thus causing non-optimal plans in complex ANDed/ORed Boolean expressions. From v2.0 and ODS11, these predicates are optimized in a regular way and hence benefit from all possible optimization strategies.

Complex Boolean expressions consisting of many AND/OR predicates are now entirely mapped to available indices if at all possible. Previously, such complex expressions could be optimized badly.

Cost estimations have been improved in order to improve JOIN orders.

It is now possible for indexed order to be utilised for outer joins, i.e. navigational walk.