Performance degradation after ALTER INDEX ... ACTIVE

[RaulWW]

A significant performance degradation was observed after executing ALTER INDEX ... ACTIVE on all indexes of a Firebird database.

Before activating the indexes (right after a backup/restore), the query ran normally and used an efficient plan.

After running ALTER INDEX ... ACTIVE for all indexes, the same query became ~40x slower and started using a completely different, suboptimal plan.

Environment

• Firebird version: 5.0.3
• OS: Windows 11
  *Database size: 22GB

Query used:
select first 1 count (NF.NFS) as REGISTROS,
NF.STATUS_NFE
from NF_SAIDA NF
join OPERACOES_FISCAIS OP
on NF.EMPRESA = OP.EMPRESA
and NF.OPF_CODIGO = OP.OPF_CODIGO
where NF.EMPRESA = 1
and (NF.DT_EMISSAO between '01.10.2025' and current_date)
and NF.DT_CANCELAMENTO is null
and (NF.STATUS_NFE is null or NF.STATUS_NFE in ('AA','AS','RE'))
and NF.STATUS <> 1
and OP.TIPO_DOCTO = 1
group by NF.STATUS_NFE
order by REGISTROS desc;

Result AFTER runnig activate all index

Plan
PLAN SORT (SORT (JOIN (OP NATURAL, NF INDEX (IDX_NFS_NFS))))

------ Performance info ------
Prepare time = 0ms
Execute time = 38s 954ms
Current memory = 7.427.312
Max memory = 7.454.928
Memory buffers = 256
Reads from disk to cache = 1.810.885
Writes from cache to disk = 0
Fetches from cache = 28.945.697

---

Result BEFORE and same result using using FB 3.0 (even if active all index, FB 3.0 mantain the same performance);

Plan
PLAN SORT (SORT (JOIN (OP NATURAL, NF INDEX (FK_NFS_OPERACAO))))

------ Performance info ------
Prepare time = 0ms
Execute time = 2s 828ms
Current memory = 3.819.120
Max memory = 6.291.408
Memory buffers = 256
Reads from disk to cache = 161.028
Writes from cache to disk = 0
Fetches from cache = 2.421.604

@dyemanov already have a sample database;

Question

Is this behavior expected?
After activating all indexes, the optimizer seems to choose a clearly worse plan, resulting in a huge performance drop, even though the dataset and query remain identical.

Additional notes

The query results are identical in both scenarios.
The only change was executing ALTER INDEX ... ACTIVE.
After activation, the optimizer consistently picks a different and much slower index.

[RaulWW]

Additional findings

I've performed several more tests with different databases and queries, and the behavior is consistent.
After running SET STATISTICS or ALTER INDEX ... ACTIVE, the performance degradation is massive — in some cases, queries that previously executed in less than a second now take around 3 minutes to complete.

It also seems that after recalculating index statistics, the optimizer completely changes the chosen PLAN, often switching from an efficient index (like FK_NFS_OPERACAO) to a much slower one (IDX_NFS_NFS), even though the dataset and conditions remain exactly the same.

Today test case:
BEFORE ALTER INDEX OR SET STATICS

select max (NF.DT_EMISSAO)
from NFS_ITENS NI
join NF_SAIDA NF
on NF.EMPRESA = NI.EMPRESA
and NF.NFS = NI.NFS
join OPERACOES_FISCAIS OPF
on OPF.EMPRESA = NF.EMPRESA
and OPF.OPF_CODIGO = NF.OPF_CODIGO
where NI.EMPRESA = 1
and NI.PRO_CODIGO = 101929
and NI.QUANTIDADE > NI.QTDE_DEVOLVIDA
and NF.STATUS = 2
and OPF.ATUALIZA_ESTATISTICAS = 'S'

PLAN JOIN (HASH (NI INDEX (FK_NFSI_PRODUTO), OPF INDEX (RDB$PRIMARY29)), NF INDEX (RDB$PRIMARY58))

Adapted Plan

PLAN JOIN (HASH (NI INDEX (FK_NFSI_PRODUTO), OPF INDEX (INTEG_95)), NF INDEX (INTEG_208))
------ Performance info ------
Prepare time = 0ms
Execute time = 0ms
Avg fetch time = 0,00 ms
Current memory = 7.063.600
Max memory = 7.112.608
Memory buffers = 256
Reads from disk to cache = 0
Writes from cache to disk = 0
Fetches from cache = 165

AFTER ALTER INDEX OR SET STATICS

Adapted Plan

PLAN JOIN (NF INDEX (IDX_NFS_NFS), OPF INDEX (INTEG_95), NI INDEX (FK_NFS_ITENS))
------ Performance info ------
Prepare time = 0ms
Execute time = 3m 0s 375ms
Avg fetch time = 180.375,00 ms
Current memory = 9.578.384
Max memory = 9.606.112
Memory buffers = 256
Reads from disk to cache = 531.110
Writes from cache to disk = 0
Fetches from cache = 25.506.659

Question / Concern
Honestly, this behavior is quite concerning.
It doesn’t seem reasonable that simply recalculating index statistics or reactivating indexes would cause such a drastic degradation in query performance — especially when the data and conditions remain identical.

Is this really the expected behavior?
If so, could you please explain the rationale behind the optimizer choosing a completely different and much slower plan after the statistics are updated?
Otherwise, this looks like a possible regression or a bug in how Firebird 5.0 handles updated index selectivity.

[mrotteveel]

Honestly, this behavior is quite concerning.
It doesn’t seem reasonable that simply recalculating index statistics or reactivating indexes would cause such a drastic degradation in query performance — especially when the data and conditions remain identical.

It is entirely expected that recalculating index statistics changes the plans of queries, because the optimizer decisions are influenced by those statistics. That in your case the optimizer decides on a badly performing plan is unfortunate and could be considered a bug, but I'm not sure if you've provided sufficient information right now for further analysis.

Also, as currently phrased this seems to be a question ("is this expected"), and not a bug report, and as such, it should have been asked on firebird-support or on Discussions.

I've considered moving it to Discussions, but as you explicitly mention that @dyemanov already has a copy of the database, I am wondering if you did intend it as a bug report.

Could you (or maybe @dyemanov) please clarify your intent.

[dyemanov]

At least one of the reported issues is really a bug. I will look at others after resolving the first one.

[RaulWW]

@mrotteveel
Thank you for the clarification. Yes, I understand that changes in index statistics can lead the optimizer to choose different plans - that's expected.
My specific concern is the magnitude of the degradation (from less than 1 second to 3+ minutes) and the fact that the optimizer consistently chooses clearly worse plans after updating statistics, even when the data remains identical.
My intention was to report this as a bug, especially considering that:

The behavior is consistent across multiple databases and different queries
The degradation is extreme (not an acceptable performance variation)
Firebird 3.0 doesn't exhibit this behavior with the same database and queries
@dyemanov already has a sample database for analysis

I apologize if the initial phrasing seemed more like a question than a bug report. I should have been clearer about my intent.
@dyemanov
Thank you for confirming that at least one of the reported issues is indeed a bug. I appreciate you taking the time to investigate this.
Please let me know if you need any additional information, test cases, or database samples to help diagnose the other scenarios I've reported.

If need another database for this scenario (22GB) i can send too