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
FB3.0.4.33063 vs FB3.0.5.33100 manual plan cause "index cannot be used in the specified plan" [CORE6024] #6274
Comments
Modified by: @livius2Version: 3.0.5 [ 10885 ] |
Modified by: @livius2description: Something was broken between versions FB3.0.4.33063 vs FB3.0.5.33100 Also look why this ordered plan is not default generated by the engine. CREATE TABLE WPLATA CREATE INDEX IXA_WPLATA__KONTRAHENT__PK ON WPLATA (WPLATA_KONTRAHENT_ID,DYR_ID); ----------------------------------------------------------------------------------------------------- SELECT ----------------------------------------------------------------------------------------------------- index PK_WPLATA cannot be used in the specified plan. engine generate PLAN SORT (W INDEX (IXA_WPLATA__KONTRAHENT__PK)) which is not efficient => Something was broken between versions FB3.0.4.33063 vs FB3.0.5.33100 Also look why this ordered plan is not default generated by the engine. CREATE TABLE WPLATA CREATE INDEX IXA_WPLATA__KONTRAHENT__PK ON WPLATA (WPLATA_KONTRAHENT_ID,DYR_ID); ----------------------------------------------------------------------------------------------------- SELECT ----------------------------------------------------------------------------------------------------- index PK_WPLATA cannot be used in the specified plan. engine generate PLAN SORT (W INDEX (IXA_WPLATA__KONTRAHENT__PK)) which is not efficient |
Modified by: @dyemanovassignee: Dmitry Yemanov [ dimitr ] |
Commented by: @dyemanov What was the generated plan in FB 3.0.4.33063 *without* the manually provided one? |
Commented by: @livius2 PLAN SORT (W INDEX (IXA_WPLATA__KONTRAHENT__PK)) |
Modified by: @dyemanovstatus: Open [ 1 ] => Resolved [ 5 ] resolution: Fixed [ 1 ] Fix Version: 3.0.5 [ 10885 ] |
Modified by: @pavel-zotovstatus: Resolved [ 5 ] => Resolved [ 5 ] QA Status: No test => Done with caveats Test Details: Found issue on FB 4.0. |
Commented by: @livius2 Dmitry, thank you for the fix about storing manual plan. May i ask about considering fix for plan genereted by engine to be ordered and indexed in the same time? |
Submitted by: @livius2
Something was broken between versions FB3.0.4.33063 vs FB3.0.5.33100
Under FB3.0.4.33063 there was possibility to specify ordered plan for this query now in FB3.0.5.33100 it is not possible.
Also look why this ordered plan is not default generated by the engine.
CREATE TABLE WPLATA
(
DYR_ID Smallint NOT NULL,
OKRES_NUMER Char(7) NOT NULL,
INSP_ID Smallint NOT NULL,
KONTO_ID Smallint NOT NULL,
WPLATA_DATA_WYCIAGU Date NOT NULL,
WPLATA_NR_WYCIAGU Varchar(10) NOT NULL,
WPLATA_NR_POZYCJI Smallint NOT NULL,
WPLATA_KONTRAHENT_ID Integer,
CONSTRAINT PK_WPLATA PRIMARY KEY (DYR_ID,INSP_ID,KONTO_ID,WPLATA_DATA_WYCIAGU,WPLATA_NR_WYCIAGU,WPLATA_NR_POZYCJI)
);
CREATE INDEX IXA_WPLATA__KONTRAHENT__PK ON WPLATA (WPLATA_KONTRAHENT_ID,DYR_ID);
-----------------------------------------------------------------------------------------------------
SELECT
W.DYR_ID
, W.INSP_ID
, W.KONTO_ID
, W.WPLATA_DATA_WYCIAGU
, W.WPLATA_NR_WYCIAGU
, W.WPLATA_NR_POZYCJI
FROM
WPLATA W
WHERE
W.WPLATA_KONTRAHENT_ID IN (1452)
AND W.DYR_ID = 6
PLAN(W ORDER PK_WPLATA INDEX(IXA_WPLATA__KONTRAHENT__PK))
ORDER BY
W.DYR_ID
, W.INSP_ID
, W.KONTO_ID
, W.WPLATA_DATA_WYCIAGU
, W.WPLATA_NR_WYCIAGU
, W.WPLATA_NR_POZYCJI
-----------------------------------------------------------------------------------------------------
index PK_WPLATA cannot be used in the specified plan.
engine generate PLAN SORT (W INDEX (IXA_WPLATA__KONTRAHENT__PK)) which is not efficient
====== Test Details ======
Found issue on FB 4.0.
Sent letter to dimitr, 20.12.2019 23:51, waiting for reply.
The text was updated successfully, but these errors were encountered: