Issue Details (XML | Word | Printable)

Key: CORE-5481
Type: Bug Bug
Status: Closed Closed
Resolution: Fixed
Priority: Major Major
Assignee: Dmitry Yemanov
Reporter: yakovhrebtov
Votes: 0
Watchers: 2
Operations

If you were logged in you would be able to see more operations.
Firebird Core

Available indices are not used in some cases if ORDER BY expression is a filtered one

Created: 12/Feb/17 04:18 PM   Updated: 09/Dec/18 01:19 PM
Component/s: Engine
Affects Version/s: 4.0 Initial, 3.0.0, 3.0.1
Fix Version/s: 3.0.4

Issue Links:
Relate

QA Status: Done successfully


 Description  « Hide
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 CORE-5070), but it also hides possibilities to use another index FK_BALANCES_ORGACCOUNTS.


 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Dmitry Yemanov added a comment - 20/Nov/18 06:24 PM
The primary reason was fixed in 3.0.4. Other aspects are fixed for 3.0.5.