FirebirdSQL
Performance degradation after ALTER INDEX ... ACTIVE
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.
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.
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.
At least one of the reported issues is really a bug. I will look at others after resolving the first one.
@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