Query hangs when using forced PLAN that works automatically in Firebird 5

[RaulWW]

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

1. 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)))
2. 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.

[dyemanov]

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.

[RaulWW]

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.

[dyemanov]

Downloaded, thanks.

[RaulWW]

@dyemanov
Hello, did you test it? Any news? What you think about it?

[dyemanov]

I was able to reproduce the issue, it's being investigated.