Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Firebird 5.0.1 is not using index when all records are null and left join is converted to inner join #8344

Open
dsonda opened this issue Dec 7, 2024 · 0 comments

Comments

@dsonda
Copy link

dsonda commented Dec 7, 2024

Given the query below:

SELECT
ITEM_NOTA_COMPRA.INC_ID
FROM ITEM_NOTA_COMPRA
LEFT JOIN NOTA_COMPRA
ON (ITEM_NOTA_COMPRA.NTC_ID = NOTA_COMPRA.NTC_ID)
WHERE (ITEM_NOTA_COMPRA.INC_ID_COMPLEMENTADO = 651321)
AND (NOTA_COMPRA.NTC_SITUACAO = 'Finalizada')

Field INC_ID_COMPLEMENTADO is a foreign key and all record values are null (index stats = 1).

Firebird 2.5.9 uses the FK index from INC_ID_COMPLEMENTADO field:

PLAN JOIN (ITEM_NOTA_COMPRA INDEX (FK_INC_INC_COMPLEMENTADO), NOTA_COMPRA INDEX (PK_NOTA_COMPRA))
Records fetched = 0
Prepare time = 0ms
Execute time = 0ms
Reads from disk to cache = 0
Fetches from cache = 24

Firebird 5.0.1 converts to inner join and do a full scan on right table:

PLAN JOIN (NOTA_COMPRA NATURAL, ITEM_NOTA_COMPRA INDEX (FK_ITEM_NTC_NOTA_COMPRA))
Records fetched = 0
Prepare time = 0ms
Execute time = 1s 0ms
Reads from disk to cache = 13.592
Fetches from cache = 871.441

With "OuterJoinConversion = false", they use the same plan.

Workaround:
Create an "is not null" partial index on field INC_ID_COMPLEMENTADO (stats = 0).
But, then you have two indexes on same field, because it's a foreign key.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests

1 participant