FirebirdSQL
Using exists in Firebird 5.0.1 uses a different and slow plan compared to just the select statement
Given the query below:
SELECT
IIF(EXISTS(
SELECT ORDEM_PRODUCAO.ORP_ID
FROM ITEM_SEPARACAO_PEDIDO_VENDA
INNER JOIN ORDEM_PRODUCAO
ON (ITEM_SEPARACAO_PEDIDO_VENDA.ISP_ID = ORDEM_PRODUCAO.ISP_ID)
WHERE (ITEM_SEPARACAO_PEDIDO_VENDA.IPV_ID = 9437253)), 'Sim', 'Não') as TEM_OP
FROM RDB$DATABASE
All records in ORDEM_PRODUCAO have ISP_ID field null. So, the FK index for this field has statistics 1.
Firebird 2.5.9:
PLAN JOIN (ITEM_SEPARACAO_PEDIDO_VENDA INDEX (FK_ITEM_SEP_PDV_ITEM_PEDIDO), ORDEM_PRODUCAO INDEX (FK_ORP_ISP)) PLAN (RDB$DATABASE NATURAL) Prepare time = 16ms Execute time = 0ms Avg fetch time = 0,00 ms Current memory = 19.186.008 Max memory = 19.255.648 Memory buffers = 2.048 Reads from disk to cache = 0 Writes from cache to disk = 0 Fetches from cache = 385
Firebird 5.0.1:
PLAN JOIN (ORDEM_PRODUCAO NATURAL, ITEM_SEPARACAO_PEDIDO_VENDA INDEX (PK_ITEM_SEPARACAO_PEDIDO_VENDA)) PLAN (RDB$DATABASE NATURAL) Prepare time = 16ms Execute time = 34s 718ms Avg fetch time = 34.718,00 ms Current memory = 4.399.790.672 Max memory = 4.400.434.560 Memory buffers = 262.144 Reads from disk to cache = 310.931 Writes from cache to disk = 0 Fetches from cache = 76.558.838
It seems to be the same problem as described in https://github.com/FirebirdSQL/firebird/issues/8344.
But, in Firebird 5, executing just the select, without the exists, has the same result as Firebird 2.5.9.
Firebird 5.0.1:
SELECT ORDEM_PRODUCAO.ORP_ID
FROM ITEM_SEPARACAO_PEDIDO_VENDA
INNER JOIN ORDEM_PRODUCAO
ON (ITEM_SEPARACAO_PEDIDO_VENDA.ISP_ID = ORDEM_PRODUCAO.ISP_ID)
WHERE (ITEM_SEPARACAO_PEDIDO_VENDA.IPV_ID = 9437253)
PLAN JOIN (ITEM_SEPARACAO_PEDIDO_VENDA INDEX (FK_ITEM_SEP_PDV_ITEM_PEDIDO), ORDEM_PRODUCAO INDEX (FK_ORP_ISP)) Prepare time = 0ms Execute time = 0ms Current memory = 4.399.838.576 Max memory = 4.400.434.560 Memory buffers = 262.144 Reads from disk to cache = 3 Writes from cache to disk = 0 Fetches from cache = 4
Can you provide the test database (with removed / anonymized unrelated data)?
I created a database with just the two tables and columns used in the statements and inserted the same data as the original one. You can download it from here