FB5 uses PK for ordered plan even if less count of fields matching index exists.
use all scripts and data generator from
#7904
and add additional scripts after:
CREATE TABLE DYREKCJA
(
DYR_ID SMALLINT NOT NULL PRIMARY KEY
);
commit;
INSERT INTO DYREKCJA
SELECT DISTINCT DYR_ID FROM ROZLICZENIE;
ALTER TABLE ROZLICZENIE ADD CONSTRAINT FK_ROZLICZENIE__DYREKCJA FOREIGN KEY (DYR_ID) REFERENCES DYREKCJA (DYR_ID);
and then run this query:
SELECT
RL.DYR_ID AS "Dyrekcja"
, COUNT(*) AS "RL"
FROM
ROZLICZENIE RL
WHERE
RL.OKRES_NUMER = '15'
AND RL.DOK_ROZLICZENIOWY_ID IN ('1')
GROUP BY
RL.DYR_ID
/* PLAN (RL ORDER FK_ROZLICZENIE__DYREKCJA INDEX (ROZLICZENIE_FK4)) */FB5 plan:
PLAN (RL ORDER PK_ROZLICZENIE INDEX (ROZLICZENIE_FK4))
but should be:
PLAN (RL ORDER FK_ROZLICZENIE__DYREKCJA INDEX (ROZLICZENIE_FK4))
Forgot to add that result is slower in our cases by >40% as index with 5 or more columns is used instead of 1 column index.
FB5 uses PK for ordered plan even if less count of fields matching index exists.
use all scripts and data generator from
#7904
and add additional scripts after:
and then run this query:
FB5 plan:
PLAN (RL ORDER PK_ROZLICZENIE INDEX (ROZLICZENIE_FK4))
but should be:
PLAN (RL ORDER FK_ROZLICZENIE__DYREKCJA INDEX (ROZLICZENIE_FK4))
Forgot to add that result is slower in our cases by >40% as index with 5 or more columns is used instead of 1 column index.