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

Badly selective index could be used for extra filtering even if selective index is used for sorting [CORE5435] #5707

Closed
firebird-automations opened this issue Jan 4, 2017 · 11 comments

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: Paquito Ines (paquito)

Attachments:
Databases.zip

It seems that Firebird 3 is sometimes choosing the index with less selectivity, which can have a serious
effect on performance. We have seen this behaviour in several queries.

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 2.1/2.5:
PLAN (TABLE1 ORDER TABLE1_F1_ID INDEX (TABLE1_F1_ID))
0.002 seconds

Firebird3:
PLAN (TABLE1 ORDER TABLE1_F1_ID INDEX (TABLE1_F2))
Select Expression
-> Filter
-> Table "TABLE1" Access By ID
-> Index "TABLE1_F1_ID" Range Scan (partial match: 1/2)
-> Bitmap
-> Index "TABLE1_F2" Range Scan (full match)
0.240 seconds

-

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: e4f7a6d abc072a

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

reporter: Dmitry Yemanov [ dimitr ] => Paquito Ines [ paquito ]

assignee: Dmitry Yemanov [ dimitr ]

Attachment: Databases.zip [ 13052 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

Fix Version: 3.0.2 [ 10785 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

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

resolution: Fixed [ 1 ]

Fix Version: 4.0 Alpha 1 [ 10731 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: Paquito Ines (paquito)

We have tested the issue with the Nightly Build, and, though the plan has changed and no longer uses the low selectivity index, the performance has not improved and is even slightly worse.

Firebird-3.0.2.32664-0_x64 (nightly build):
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

Maybe is using TABLE1_F1_ID only for ordered retrieval and not for filtering?

Forcing the Firebird 2.5 plan works well:
select *
from TABLE1 where FIELD1 is null and FIELD2=0
PLAN (TABLE1 ORDER TABLE1_F1_ID INDEX (TABLE1_F1_ID))
order by FIELD1, ID

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

@firebird-automations
Copy link
Collaborator Author

Commented by: Paquito Ines (paquito)

Further testing with Firebird 3.0.2.nightly shows a possible problem with the "FIELD1 is null" condition, as it seems to not be using the index for filtering it, but it is working well if the condition is "FIELD1=0", tough both queries have the same plan.

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

@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

It appears to be a completely unrelated bug, related to NULLs filtering inside the navigational index scan. I'm investigating.

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

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

QA Status: No test => Deferred

Test Details: Waiting for reply on letter 19.01.2017 08:06. Performance in 3.0.2 and above was improved only for NOT-null values (as it was already noticed here).

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

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

QA Status: Deferred => Done successfully

Test Details: Waiting for reply on letter 19.01.2017 08:06. Performance in 3.0.2 and above was improved only for NOT-null values (as it was already noticed here). =>

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

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

@firebird-automations
Copy link
Collaborator Author

Commented by: Paquito Ines (paquito)

Do you want that we submit a new report for this new bug, so it will be easily tracked?

@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

Please create one. I already have a fix, just need to double check.

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