FirebirdSQL
Query hangs when using forced PLAN that works automatically in Firebird 5
Environment Firebird Version: 5.0.3 Operating System: Windows 11 Architecture: x64 Page Size: 4096
Problem Description When executing a query, Firebird 5 automatically generates and successfully uses a specific execution plan in ~1 second. However, when the exact same plan is manually specified using the PLAN clause, the query hangs indefinitely. Expected Behavior The manually forced plan should execute with the same performance as the automatically chosen plan. Actual Behavior Query hangs/freezes when using the forced plan, requiring connection termination.
Reproduction Steps
- Working Query (Automatic Plan) SELECT FIRST 100 N.NFS, N.NOTA_FISCAL, N.CHAVE_NFE, N.SERIE, N.DT_EMISSAO, N.TOTAL_NOTA, N.CLI_CODIGO, N.PDV_CODIGO, N.PDV_NUMERO_VENDA, N.CP_CODIGO, COALESCE(N.CLI_NOME, C.CLI_NOME) as CLI_NOME, N.PESO_BRUTO, N.REP_CODIGO FROM NF_SAIDA N JOIN CLIENTES C ON N.EMPRESA = C.EMPRESA AND N.CLI_CODIGO = C.CLI_CODIGO WHERE N.EMPRESA = 1 AND N.STATUS = 2 ORDER BY N.EMPRESA, N.DT_EMISSAO DESC, N.NFS DESC, N.NOTA_FISCAL; Result: Executes successfully in ~1 second Automatic Plan Generated: PLAN SORT (HASH (N INDEX (IDX_NFS_STATUS), C INDEX (FK_CLI_FP_ENTRADA)))
- Hanging Query (Forced Plan) same query but adding: PLAN SORT (HASH (N INDEX (IDX_NFS_STATUS), C INDEX (FK_CLI_FP_ENTRADA)))
Result: Query hangs indefinitely Additional Information Tested Variations (All Hang) PLAN SORT (N INDEX (IDX_NFS_STATUS), C INDEX (FK_CLI_FP_ENTRADA)) PLAN (N INDEX (IDX_NFS_STATUS), C INDEX (FK_CLI_FP_ENTRADA)) PLAN SORT (MERGE (N INDEX (IDX_NFS_STATUS), C INDEX (FK_CLI_FP_ENTRADA)))
Database Schema (Relevant Parts) NF_SAIDA table structure CREATE TABLE NF_SAIDA ( EMPRESA INTEGER, NFS INTEGER, STATUS INTEGER, DT_EMISSAO DATE, CLI_CODIGO INTEGER, -- ... other columns );
-- CLIENTES table structure
CREATE TABLE CLIENTES (
EMPRESA INTEGER,
CLI_CODIGO INTEGER,
CLI_NOME VARCHAR(100),
-- ... other columns
);
-- Relevant indexes CREATE INDEX IDX_NFS_STATUS ON NF_SAIDA (EMPRESA, STATUS); CREATE INDEX FK_CLI_FP_ENTRADA ON CLIENTES (EMPRESA, CLI_CODIGO); Steps Taken
Recomputed all statistics using SET STATISTICS No database structure changes made Same database, same data, same connection Tested with different PLAN variations All forced plans hang, automatic plan works perfectly
Monitoring Information When the query hangs with forced plan:
No error messages in firebird.log Connection remains active but unresponsive Must terminate connection to recover No apparent memory or CPU spikes
Hypothesis This appears to be a bug in Firebird 5 where the plan parser or executor handles manually specified plans differently than automatically generated ones, even when they appear identical.
Note: I can provide a minimal test database if needed to reproduce the issue.
Column NOTA_FISCAL is missing in the table structure. And I failed to reproduce the issue with the steps provided, so a complete test case would be appreciated.
hello @dyemanov even if you remove from order by ('NOTA_FISCAL') , it hangs.
another problem founded: We have identified significant performance regressions in Firebird 5.0.3 compared to 3.0: -- Same database, same SELECT, no changes whatsoever select first 100 N.NFS, N.NOTA_FISCAL, N.CHAVE_NFE, N.SERIE, N.DT_EMISSAO, N.TOTAL_NOTA, N.CLI_CODIGO, N.PDV_CODIGO, N.PDV_NUMERO_VENDA, N.CP_CODIGO, COALESCE(N.CLI_NOME, C.CLI_NOME) as CLI_NOME, N.PESO_BRUTO, N.REP_CODIGO, O.OPERACAO from NF_SAIDA N join CLIENTES C on N.EMPRESA = C.EMPRESA and N.CLI_CODIGO = C.CLI_CODIGO join OPERACOES_FISCAIS O on O.EMPRESA = N.EMPRESA and O.OPF_CODIGO = N.OPF_CODIGO where N.EMPRESA = 1 and N.STATUS = 2 and N.DT_EMISSAO between '01.01.2020' and current_date order by N.EMPRESA, N.DT_EMISSAO desc, N.NFS desc
Performance Comparison Firebird 3.0 (Optimal Performance) Plan: PLAN SORT (JOIN (N INDEX (IDX_NFS_EMISSAO), O INDEX (RDB$PRIMARY29), C INDEX (RDB$PRIMARY2)))
Execution Plan: Select Expression -> First N Records -> Sort (record length: 348, key length: 32) -> Nested Loop Join (inner) -> Filter -> Table "NF_SAIDA" as "N" Access By ID -> Bitmap -> Index "IDX_NFS_EMISSAO" Range Scan (lower bound: 2/2, upper bound: 2/2) -> Filter -> Table "OPERACOES_FISCAIS" as "O" Access By ID -> Bitmap -> Index "RDB$PRIMARY29" Unique Scan -> Filter -> Table "CLIENTES" as "C" Access By ID -> Bitmap -> Index "RDB$PRIMARY2" Unique Scan
Performance Info:
- Prepare time = 16ms
- Execute time = 1s 390ms ✅
- Avg fetch time = 40,88 ms
- Current memory = 12.720.224
- Max memory = 79.826.160
- Memory buffers = 2.048
- Reads from disk to cache = 89.676
- Writes from cache to disk = 1
- Fetches from cache = 1.661.109 ✅
Firebird 5.0.3 (Performance Regression) Plan: PLAN SORT (JOIN (HASH (N INDEX (IDX_NFS_EMISSAO), O INDEX (RDB$PRIMARY29)), C INDEX (RDB$PRIMARY2)))
Execution Plan: Select Expression -> First N Records -> Refetch -> Sort (record length: 84, key length: 32) -> Filter -> Nested Loop Join (inner) -> Hash Join (inner) ⚠️ -> Filter -> Table "NF_SAIDA" as "N" Access By ID -> Bitmap -> Index "IDX_NFS_EMISSAO" Range Scan (lower bound: 2/2, upper bound: 2/2) -> Record Buffer (record length: 33) -> Filter -> Table "OPERACOES_FISCAIS" as "O" Access By ID -> Bitmap -> Index "RDB$PRIMARY29" Range Scan (partial match: 1/2) -> Filter -> Table "CLIENTES" as "C" Access By ID -> Bitmap -> Index "RDB$PRIMARY2" Unique Scan
Performance Info:
- Prepare time = 0ms
- Execute time = 21s 422ms ❌ (15x SLOWER!)
- Avg fetch time = 714,07 ms
- Current memory = 13.591.232
- Max memory = 37.724.448
- Memory buffers = 2.048
- Reads from disk to cache = 89.982
- Writes from cache to disk = 1
- Fetches from cache = 42.854.229 ❌ (26x MORE FETCHES!)
@dyemanov Here's the download link for the test database. Could you please let me know here when you've downloaded it? Thank you.