Firebird 5 wrong plan

[EPluribusUnum]

Hi *!

I can send the database on request.

SELECT 1
FROM ktrt rt
    JOIN ktbt bt ON bt.ktrt_id = rt.id
    JOIN ktpu kp ON kp.ktbt_id = bt.id
    LEFT OUTER JOIN pu_btet_helyesbitett pt ON pt.id = kp.pu_btet_id
    LEFT OUTER JOIN pu_biz pb ON pb.id = pt.pbiz_id
    LEFT OUTER JOIN ktmert_valtoszam vsz ON vsz.ktfj_id = pt.termek_id AND
          vsz.mert_id_rol = pt.mert_id AND
          vsz.mert_id_re = rt.mert_id
    LEFT OUTER JOIN pu_biz pbh ON pbh.id = pt.h_pbiz_id
WHERE rt.id = 6 AND
      pb.pbizkapcs = 'N'

FB30 and FB40 : OK

PLAN (PT PBH INDEX (PU_BIZ_PK))
PLAN JOIN (JOIN (JOIN (JOIN (JOIN (KP NATURAL, BT INDEX (KTBT_PK), RT INDEX (PK_KTRT)), JOIN (JOIN (JOIN (PT PTO INDEX (PU_BTET_PK), PT BK INDEX (PU_BIZKAPCS_PBIZ_ID)), PT PTH INDEX (PU_BTET_PK)), PT PTU INDEX (PU_BTET_UK))), PB INDEX (PU_BIZ_PK)), VSZ INDEX (KTMERT_VALTOSZAM_UK1)), PBH INDEX (PU_BIZ_PK))

Enchanced Info:
+-------------------------------+-----------+-----------+-------------+---------+---------+---------+----------+----------+----------+
|          Table Name           |  Records  |  Indexed  | Non-Indexed | Updates | Deletes | Inserts | Backouts |  Purges  | Expunges |
|                               |   Total   |   reads   |    reads    |         |         |         |          |          |          |
+-------------------------------+-----------+-----------+-------------+---------+---------+---------+----------+----------+----------+
|KTBT                           |         0 |         1 |           0 |       0 |       0 |       0 |        0 |        0 |        0 |
|KTPU                           |         0 |         0 |           1 |       0 |       0 |       0 |        0 |        0 |        0 |
+-------------------------------+-----------+-----------+-------------+---------+---------+---------+----------+----------+----------+

FB50: wrong (SubQueryConversion has no effect)

PLAN (PT PBH INDEX (PU_BIZ_PK))
PLAN JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (PT PTO NATURAL, PT BK INDEX (PU_BIZKAPCS_PBIZ_ID)), PT PTH INDEX (PU_BTET_PK)), PT PTU INDEX (PU_BTET_UK)), PB INDEX (PU_BIZ_PK), KP INDEX (KTPU_FK2)), BT INDEX (KTBT_PK)), RT INDEX (PK_KTRT)), VSZ INDEX (KTMERT_VALTOSZAM_UK1)), PBH INDEX (PU_BIZ_PK))

+-------------------------------+-----------+-----------+-------------+---------+---------+---------+----------+----------+----------+
|          Table Name           |  Records  |  Indexed  | Non-Indexed | Updates | Deletes | Inserts | Backouts |  Purges  | Expunges |
|                               |   Total   |   reads   |    reads    |         |         |         |          |          |          |
+-------------------------------+-----------+-----------+-------------+---------+---------+---------+----------+----------+----------+
|KTBT                           |         0 |         1 |           0 |       0 |       0 |       0 |        0 |        0 |        0 |
|KTPU                           |         0 |         1 |           0 |       0 |       0 |       0 |        0 |        0 |        0 |
|PU_BIZ                         |         0 |       634 |           0 |       0 |       0 |       0 |        0 |        0 |        0 |
|PU_BIZKAPCS                    |         0 |        20 |           0 |       0 |       0 |       0 |        0 |        0 |        0 |
|PU_BTET                        |         0 |        35 |         616 |       0 |       0 |       0 |        0 |        0 |        0 |
+-------------------------------+-----------+-----------+-------------+---------+---------+---------+----------+----------+----------+

[sim1984]

There are no subqueries in this example, so the SubQueryConversion parameter is irrelevant. However, there is an OUTER to INNER join conversion; you need to look at the OuterJoinConversion parameter.

[sim1984]

P.S. Check the performance of this query:

SELECT 1
FROM ktrt rt
    JOIN ktbt bt ON bt.ktrt_id = rt.id
    JOIN ktpu kp ON kp.ktbt_id = bt.id
    JOIN pu_btet_helyesbitett pt ON pt.id = kp.pu_btet_id
    JOIN pu_biz pb ON pb.id = pt.pbiz_id
    LEFT OUTER JOIN ktmert_valtoszam vsz ON vsz.ktfj_id = pt.termek_id AND
          vsz.mert_id_rol = pt.mert_id AND
          vsz.mert_id_re = rt.mert_id
    LEFT OUTER JOIN pu_biz pbh ON pbh.id = pt.h_pbiz_id
WHERE rt.id = 6 AND
      pb.pbizkapcs = 'N'

[EPluribusUnum]

P.S. Check the performance of this query:

No change, same plan and performance.

[EPluribusUnum]

There are no subqueries in this example, so the SubQueryConversion parameter is irrelevant. However, there is an OUTER to INNER join conversion; you need to look at the OuterJoinConversion parameter.

Turning OFF OuterJoinConversion works, FB50 gives the same result as FB30 and FB40.

[dyemanov]

Please send the test database (or download link) to firebird2 at yandex dot ru.

[EPluribusUnum]

@dyemanov , email was sent. Thank you!

[RaulWW]

to add, again @dyemanov

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 '15.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;

Plan

PLAN SORT (SORT (JOIN (OP NATURAL, NF INDEX (IDX_NFS_NFS))))
------ Performance info ------
Prepare time = 0ms
Execute time = 18s 328ms
Avg fetch time = 18.328,00 ms
Current memory = 8.388.544
Max memory = 8.416.160
Memory buffers = 256
Reads from disk to cache = 1.810.854
Writes from cache to disk = 0
Fetches from cache = 28.945.835

on FB 3 exec in 0.0500ms, and now 18s;
you have the database (sended on another topic).

a lot of query's are running 15x slower than ever before.. ;\

[hvlad]

to add, again @dyemanov

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 '15.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;

Check brackets, is it what you need ?

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 '15.10.2025' and current_date)
   and NF.DT_CANCELAMENTO is null
   and (F.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;

[sim1984]

@RaulWW There is no need to mix completely different cases in one ticket.

@EPluribusUnum has a completely different case. And the reason is pretty clear: LEFT JOINs don't cascade transform into INNER JOINs.