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

Available indices are not used in some cases if ORDER BY expression is a filtered one [CORE5481] #5751

Closed
firebird-automations opened this issue Feb 12, 2017 · 8 comments

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: yakovhrebtov (yakovhrebtov)

Is related to CORE5070
Relate to CORE5965

CREATE TABLE ORG_ACCOUNTS
(
ORGACCOUNTID BIGINT NOT NULL PRIMARY KEY
);

CREATE TABLE BALANCES
(
BALANCEID BIGINT NOT NULL,
ORGACCOUNTID BIGINT NOT NULL,
BALANCEDATE DATE NOT NULL
);

ALTER TABLE BALANCES ADD CONSTRAINT PK_BALANCES PRIMARY KEY (BALANCEID);
ALTER TABLE BALANCES ADD CONSTRAINT FK_BALANCES_ORGACCOUNTS FOREIGN KEY (ORGACCOUNTID) REFERENCES ORG_ACCOUNTS (ORGACCOUNTID);
ALTER TABLE BALANCES ADD CONSTRAINT BALANCES_BALANCEDATE_ORGACCOUNT UNIQUE (ORGACCOUNTID, BALANCEDATE);

CREATE DESCENDING INDEX BALANCES_BALANCEDATE_DESC ON BALANCES (BALANCEDATE);

select first 1 *
from Balances B
where OrgAccountID=18 and
BalanceDate<='01.01.2017'
order by BalanceDate desc;

v2.5:
PLAN (B ORDER BALANCES_BALANCEDATE_DESC INDEX (BALANCES_BALANCEDATE_ORGACCOUNT))

v3.0:
PLAN (B ORDER BALANCES_BALANCEDATE_DESC)

Correct (best) plan should be:
PLAN (B ORDER BALANCES_BALANCEDATE_DESC INDEX (FK_BALANCES_ORGACCOUNTS))

Now the funny thing:
ALTER TABLE BALANCES DROP CONSTRAINT BALANCES_BALANCEDATE_ORGACCOUNT;

v3.0:
PLAN (B ORDER BALANCES_BALANCEDATE_DESC INDEX (FK_BALANCES_ORGACCOUNTS))

I.e. existing compound index BALANCES_BALANCEDATE_ORGACCOUNT is not only ignored itself (see also CORE5070), but it also hides possibilities to use another index FK_BALANCES_ORGACCOUNTS.

Commits: 1112962 ffff0e8

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

assignee: Dmitry Yemanov [ dimitr ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

reporter: Dmitry Yemanov [ dimitr ] => yakovhrebtov [ yakovhrebtov ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

Link: This issue is related to CORE5070 [ CORE5070 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

Link: This issue relate to CORE5965 [ CORE5965 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

The primary reason was fixed in 3.0.4. Other aspects are fixed for 3.0.5.

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

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

resolution: Fixed [ 1 ]

Fix Version: 3.0.4 [ 10863 ]

@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