Available indices are not used in some cases if ORDER BY expression is a
filtered one
-------------------------------------------------------------------------------------
Key: CORE-5481
URL: http://tracker.firebirdsql.org/browse/CORE-5481
Project: Firebird Core
Issue Type: Bug
Components: Engine
Affects Versions: 3.0.1, 3.0.0, 4.0 Initial
Reporter: Dmitry Yemanov
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.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://tracker.firebirdsql.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, SlashDot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
