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

Bad performance for NULLs filtering inside a navigational index scan [CORE5489] #5759

Closed
firebird-automations opened this issue Feb 22, 2017 · 6 comments

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: Paquito Ines (paquito)

Attachments:
Databases.zip

Votes: 2

When filtering and ordering a query by the same field, Firebird 3 is capable of doing the filtering directly inside the navigational index, but this seems to not be working well if the filtering condition is "is null". In this case, the performance is similar to not using the index. However, the performance is the expected if the filtering condition is "=0", though both queries have the same plan.

This must be tested with Firebird-3.0.2.32664 (nightly build), after correction of CORE5435. It can also be tested with Firebird 3.0.1 eliminating the "FIELD2=0" condition.

In this example (we have attached in databases.zip the databases for Firebird 2.5 and Firebird 3):
-Table1 has around 300000 records.
-Field1 has 15000 different values with 20 records each one.
-Field2 has 2 different values (0 with 200000 records, and 1 with 100000 records).
-Both databases were tested immediately after a backup/restore cycle and in the same computer.
-The times are measured in the second execution of each query (though the first execution follow the same pattern).

Query:
select *
from TABLE1 where FIELD1 is null and FIELD2=0
order by FIELD1, ID

Firebird-3.0.2.32664-0_x64 (nightly build):
select *
from TABLE1 where FIELD1 is null and FIELD2=0
order by FIELD1, ID

PLAN (TABLE1 ORDER TABLE1_F1_ID)
Select Expression
-> Filter
-> Table "TABLE1" Access By ID
-> Index "TABLE1_F1_ID" Range Scan (partial match: 1/2)
0.290 seconds

Firebird-3.0.2.32664-0_x64 (nightly build):
select *
from TABLE1 where FIELD1=0 and FIELD2=0
order by FIELD1, ID;

PLAN (TABLE1 ORDER TABLE1_F1_ID)
Select Expression
-> Filter
-> Table "TABLE1" Access By ID
-> Index "TABLE1_F1_ID" Range Scan (partial match: 1/2)
0.002 s

However, in Firebird 2.5, both "FIELD1 is null" and "FIELD1=0" queries have same plan and same timing:
PLAN (TABLE1 ORDER TABLE1_F1_ID INDEX (TABLE1_F1_ID))
0.002 s

-

CREATE TABLE "TABLE1"
(
"ID" INTEGER NOT NULL,
"FIELD1" INTEGER,
"FIELD2" INTEGER,
CONSTRAINT "TABLE1_PK" PRIMARY KEY ("ID")
);

CREATE INDEX "TABLE1_F1_ID" ON "TABLE1"("FIELD1", "ID");
CREATE INDEX "TABLE1_F2" ON "TABLE1"("FIELD2");

Commits: 15fa0af 046fb37

@firebird-automations
Copy link
Collaborator Author

Modified by: Paquito Ines (paquito)

Attachment: Databases.zip [ 13072 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

assignee: Dmitry Yemanov [ dimitr ]

@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

In fact, this bug exists in all FB versions. The plan chosen by v2.5 just hides the issue. It manifests itself when:

1) index is compound
2) IS NULL lookup is done using the first segment (partial match)
3) ORDER BY is mapped to the same index (limited navigational scan is performed)

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

status: Open [ 1 ] => Resolved [ 5 ]

resolution: Fixed [ 1 ]

Fix Version: 4.0 Alpha 1 [ 10731 ]

Fix Version: 3.0.2 [ 10785 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

status: Resolved [ 5 ] => Resolved [ 5 ]

QA Status: No test => Done successfully

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

status: Resolved [ 5 ] => Closed [ 6 ]

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment