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

FB3.0.4.33063 vs FB3.0.5.33100 manual plan cause "index cannot be used in the specified plan" [CORE6024] #6274

Closed
firebird-automations opened this issue Mar 15, 2019 · 9 comments

Comments

@firebird-automations
Copy link
Collaborator

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.

@firebird-automations
Copy link
Collaborator Author

Modified by: @livius2

Version: 3.0.5 [ 10885 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: @livius2

probably this was introduced in
a741300

@firebird-automations
Copy link
Collaborator Author

Modified by: @livius2

description: 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,
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_DATA_WPLATY
, 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

=>

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

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

assignee: Dmitry Yemanov [ dimitr ]

@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

What was the generated plan in FB 3.0.4.33063 *without* the manually provided one?

@firebird-automations
Copy link
Collaborator Author

Commented by: @livius2

PLAN SORT (W INDEX (IXA_WPLATA__KONTRAHENT__PK))

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

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

resolution: Fixed [ 1 ]

Fix Version: 3.0.5 [ 10885 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

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

QA Status: No test => Done with caveats

Test Details: Found issue on FB 4.0.
Sent letter to dimitr, 20.12.2019 23:51, waiting for reply.

@firebird-automations
Copy link
Collaborator Author

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?
PLAN(W ORDER PK_WPLATA INDEX(IXA_WPLATA__KONTRAHENT__PK))

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

No branches or pull requests

2 participants