Commented by: @dyemanov

Please specify also the query related to these plans.

Commented by: @livius2

here is query - but ticket is for general solution

SELECT
RL.DYR_ID
, RL.INSP_ID
, RL.OKRES_NUMER
, RL.ROZLICZ_NR
, RL.ROZLICZ_NR_POZ
, RL.DOK_ROZLICZENIOWY_ID
, RL.ROZLICZ_KWOTA_ROZLICZONA
, RL.ROZLICZ_RODZ_DZIAL_ID
, RL.ROZLICZ_OKRES_OD
, RL.ROZLICZ_OKRES_DO
, RL.UMOWA_ID
, RL.UMOWA_ID_SEQ
, RL.UMOWA_ID_POZ
, RL.ROZLICZENIE_ID
, U.TYP_UMOWY_ID
, U.RODZ_UMOWY_ID
, K.KONT_ID
, K.KONT_NAZWA
, CAST(CASE WHEN K.KONT_ID IS NULL THEN NULL ELSE FILL_TO_LENGTH(LICZ_PREFIX_DYR_ID(K.DYR_ID), 3, '0', 1) || FILL_TO_LENGTH(K.KONT_ID, 6, '0', 1) END AS VARCHAR(9)) AS KONT_ENOVA
, CAST(FILL_TO_LENGTH(LICZ_PREFIX_DYR_ID(RL.DYR_ID), 3, '0', 1) AS VARCHAR(3)) AS PREF_DYR
, LICZ_KONTO_INKASOWE(RL.DYR_ID, RL.INSP_ID, U.KONTO_PLATNIKA, RD.KONTO_INK_PART1, RD.KONTO_INK_PART4, '.') AS KONTO_INK
, WP.WPLATA_VAT_ID
, WP.KONTO_ID
, WP.WPLATA_DATA_WYCIAGU
, WP.WPLATA_NR_WYCIAGU
, WP.WPLATA_NR_POZYCJI
FROM
ROZLICZENIE RL
LEFT JOIN WPLATA WP ON RL.DYR_ID = WP.DYR_ID AND RL.INSP_ID = WP.INSP_ID AND RL.KONTO_ID = WP.KONTO_ID AND RL.WPLATA_DATA_WYCIAGU = WP.WPLATA_DATA_WYCIAGU AND RL.WPLATA_NR_WYCIAGU = WP.WPLATA_NR_WYCIAGU AND RL.WPLATA_NR_POZYCJI = WP.WPLATA_NR_POZYCJI
LEFT JOIN UMOWA U ON RL.UMOWA_ID = U.UMOWA_ID AND RL.DYR_ID = U.DYR_ID AND RL.UMOWA_ID_SEQ = U.UMOWA_ID_SEQ
LEFT JOIN RODZAJ_DZIAL RD ON RD.RODZ_DZIAL_ID = RL.ROZLICZ_RODZ_DZIAL_ID AND RD.RODZ_UMOWY_ID = U.RODZ_UMOWY_ID AND RD.TYP_UMOWY_ID = U.TYP_UMOWY_ID
LEFT JOIN DOK_ROZLICZENIOWY DR ON RL.DOK_ROZLICZENIOWY_ID = DR.DOK_ROZLICZENIOWY_ID
LEFT JOIN KONTRAHENT K ON K.DYR_ID = RL.DYR_ID AND K.KONT_ID = RL.WPLATA_KONTRAHENT_ID
LEFT JOIN WARUNKI W ON RL.UMOWA_ID = W.UMOWA_ID AND RL.DYR_ID = W.DYR_ID AND RL.UMOWA_ID_SEQ = W.UMOWA_ID_SEQ AND RL.UMOWA_ID_POZ = W.UMOWA_ID_POZ
WHERE
RL.DYR_ID IS NOT NULL
AND ((RL.DYR_ID <> 52 AND RD.rodz_dzial_program = 'T') OR (RL.DYR_ID = 52 AND W.WAR_PROGRAM_WYMAGANY = 'T'))
AND (((COALESCE(RL.ROZLICZENIE_STAN, '') = 'P') AND (RL.ROZLICZENIE_STAN_OKRES > '2017.02')) OR ((COALESCE(RL.ROZLICZENIE_STAN, '') <> 'P') AND (RL.ROZLICZ_PROGRAM <> 'T' OR (RL.ROZLICZ_PROGRAM = 'T' AND RL.ROZLICZ_PROGRAM_OKRES > '2017.02'))))
AND RL.OKRES_NUMER <= '2017.02'
AND DR.DOK_ROZLICZENIOWY_INKASO <> 'F'
AND ((RL.DYR_ID = 14 AND RL.INSP_ID = 13) OR (RL.DYR_ID = 14 AND RL.INSP_ID = 55) OR (RL.DYR_ID = 14 AND RL.INSP_ID = 61) OR (RL.DYR_ID = 14 AND RL.INSP_ID = 71) OR (RL.DYR_ID = 14 AND RL.INSP_ID = 1))
GROUP BY
RL.DYR_ID
, RL.INSP_ID
, RL.OKRES_NUMER
, RL.ROZLICZ_NR
, RL.ROZLICZ_NR_POZ
, RL.DOK_ROZLICZENIOWY_ID
, RL.ROZLICZ_KWOTA_ROZLICZONA
, RL.ROZLICZ_RODZ_DZIAL_ID
, RL.ROZLICZ_OKRES_OD
, RL.ROZLICZ_OKRES_DO
, RL.UMOWA_ID
, RL.UMOWA_ID_SEQ
, RL.UMOWA_ID_POZ
, RL.ROZLICZENIE_ID
, U.TYP_UMOWY_ID
, U.RODZ_UMOWY_ID
, K.KONT_ID
, K.KONT_NAZWA
, K.DYR_ID
, K.KONT_ID
, U.KONTO_PLATNIKA
, RD.KONTO_INK_PART1
, RD.KONTO_INK_PART4
, WP.WPLATA_VAT_ID
, WP.KONTO_ID
, WP.WPLATA_DATA_WYCIAGU
, WP.WPLATA_NR_WYCIAGU
, WP.WPLATA_NR_POZYCJI
PLAN SORT (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (RL INDEX (ROZLICZENIE_FK2,ROZLICZENIE_FK2,ROZLICZENIE_FK2,ROZLICZENIE_FK2,ROZLICZENIE_FK2), WP INDEX (WPLATA_PK)), U INDEX (UMOWA_PK)), RD INDEX (RODZAJ_DZIAL_PK)), DR INDEX (DOK_ROZLICZENIOWY_PK)), K INDEX (KONTRAHENT_PK)), W INDEX (WARUNKI_PK)))
ORDER BY
RL.DYR_ID
, RL.INSP_ID
, RL.OKRES_NUMER
, RL.ROZLICZ_NR
, RL.ROZLICZ_NR_POZ

Commented by: @dyemanov

Do you have other cases? This one so far looks like a bug to be fixed.

Commented by: @livius2

At this time have not to show you.
I always rewrite something in queries in such situation - and forgot about

But now i realize that better is to create ticket.
If i found more then i send it -
but i do not know if in this ticket or create another one? - because this title is not so strong releated

Commented by: Sean Leyne (seanleyne)

It seems that this case is really about have the engine follow the defined PLAN, since that is what the engine should be doing -- a PLAN statement is the instruction that tells the engine what indexes should be used/followed. No?

Commented by: @livius2

I have another case - but i do not know if the reason is the same because View is affected
When i do

SELECT * from (
SELECT FIRST 1 S.SENSOR_UID, S.NAZWA, http://SD.ID, SD.ID_SENSOR, SD.DT, SD.WARTOSC, SD.FLAGS
FROM SENSOR_DATA SD
INNER JOIN SENSOR S ON S.ID=SD.ID_SENSOR
WHERE S.ID=1
PLAN JOIN (SD ORDER IXDU_SENSOR_DATA__ID INDEX (FK_SENSOR_DATA__SENSOR), S INDEX (PK_SENSOR))
ORDER BY http://SD.ID DESC
)
UNION all
SELECT * from (
SELECT FIRST 1 S.SENSOR_UID, S.NAZWA, http://SD.ID, SD.ID_SENSOR, SD.DT, SD.WARTOSC, SD.FLAGS
FROM SENSOR_DATA SD
INNER JOIN SENSOR S ON S.ID=SD.ID_SENSOR
WHERE S.ID=2
PLAN JOIN (SD ORDER IXDU_SENSOR_DATA__ID INDEX (FK_SENSOR_DATA__SENSOR), S INDEX (PK_SENSOR))
ORDER BY http://SD.ID DESC
)

UNION all
SELECT * from (
SELECT FIRST 1 S.SENSOR_UID, S.NAZWA, http://SD.ID, SD.ID_SENSOR, SD.DT, SD.WARTOSC, SD.FLAGS
FROM SENSOR_DATA SD
INNER JOIN SENSOR S ON S.ID=SD.ID_SENSOR
WHERE S.ID=3
PLAN JOIN (SD ORDER IXDU_SENSOR_DATA__ID INDEX (FK_SENSOR_DATA__SENSOR), S INDEX (PK_SENSOR))
ORDER BY http://SD.ID DESC
)

UNION all
SELECT * from(
SELECT FIRST 1 S.SENSOR_UID, S.NAZWA, http://SD.ID, SD.ID_SENSOR, SD.DT, SD.WARTOSC, SD.FLAGS
FROM SENSOR_DATA SD
INNER JOIN SENSOR S ON S.ID=SD.ID_SENSOR
WHERE S.ID=4
PLAN JOIN (SD ORDER IXDU_SENSOR_DATA__ID INDEX (FK_SENSOR_DATA__SENSOR), S INDEX (PK_SENSOR))
ORDER BY http://SD.ID DESC
)

UNION all
SELECT * from(
SELECT FIRST 1 S.SENSOR_UID, S.NAZWA, http://SD.ID, SD.ID_SENSOR, SD.DT, SD.WARTOSC, SD.FLAGS
FROM SENSOR_DATA SD
INNER JOIN SENSOR S ON S.ID=SD.ID_SENSOR
WHERE S.ID=5
PLAN JOIN (SD ORDER IXDU_SENSOR_DATA__ID INDEX (FK_SENSOR_DATA__SENSOR), S INDEX (PK_SENSOR))
ORDER BY http://SD.ID DESC
)

UNION all
SELECT * from(
SELECT FIRST 1 S.SENSOR_UID, S.NAZWA, http://SD.ID, SD.ID_SENSOR, SD.DT, SD.WARTOSC, SD.FLAGS
FROM SENSOR_DATA SD
INNER JOIN SENSOR S ON S.ID=SD.ID_SENSOR
WHERE S.ID=6
PLAN JOIN (SD ORDER IXDU_SENSOR_DATA__ID INDEX (FK_SENSOR_DATA__SENSOR), S INDEX (PK_SENSOR))
ORDER BY http://SD.ID DESC
)

UNION all
SELECT * from(
SELECT FIRST 1 S.SENSOR_UID, S.NAZWA, http://SD.ID, SD.ID_SENSOR, SD.DT, SD.WARTOSC, SD.FLAGS
FROM SENSOR_DATA SD
INNER JOIN SENSOR S ON S.ID=SD.ID_SENSOR
WHERE S.ID=7
PLAN JOIN (SD ORDER IXDU_SENSOR_DATA__ID INDEX (FK_SENSOR_DATA__SENSOR), S INDEX (PK_SENSOR))
ORDER BY http://SD.ID DESC
)

plan is honored
PLAN (JOIN (SD ORDER IXDU_SENSOR_DATA__ID INDEX (FK_SENSOR_DATA__SENSOR), S INDEX (PK_SENSOR)), JOIN (SD ORDER IXDU_SENSOR_DATA__ID INDEX (FK_SENSOR_DATA__SENSOR), S INDEX (PK_SENSOR)), JOIN (SD ORDER IXDU_SENSOR_DATA__ID INDEX (FK_SENSOR_DATA__SENSOR), S INDEX (PK_SENSOR)), JOIN (SD ORDER IXDU_SENSOR_DATA__ID INDEX (FK_SENSOR_DATA__SENSOR), S INDEX (PK_SENSOR)), JOIN (SD ORDER IXDU_SENSOR_DATA__ID INDEX (FK_SENSOR_DATA__SENSOR), S INDEX (PK_SENSOR)), JOIN (SD ORDER IXDU_SENSOR_DATA__ID INDEX (FK_SENSOR_DATA__SENSOR), S INDEX (PK_SENSOR)), JOIN (SD ORDER IXDU_SENSOR_DATA__ID INDEX (FK_SENSOR_DATA__SENSOR), S INDEX (PK_SENSOR)))

but when i create view

CREATE VIEW VSENSOR3 (SENSOR_UID, NAZWA, ID, ID_SENSOR, DT, WARTOSC, FLAGS)
AS ....

plan is changed dramatically

PLAN (SORT (JOIN (R S INDEX (PK_SENSOR), R SD INDEX (FK_SENSOR_DATA__SENSOR))), SORT (JOIN (R S INDEX (PK_SENSOR), R SD INDEX (FK_SENSOR_DATA__SENSOR))), SORT (JOIN (R S INDEX (PK_SENSOR), R SD INDEX (FK_SENSOR_DATA__SENSOR))), SORT (JOIN (R S INDEX (PK_SENSOR), R SD INDEX (FK_SENSOR_DATA__SENSOR))), SORT (JOIN (R S INDEX (PK_SENSOR), R SD INDEX (FK_SENSOR_DATA__SENSOR))), SORT (JOIN (R S INDEX (PK_SENSOR), R SD INDEX (FK_SENSOR_DATA__SENSOR))), SORT (JOIN (R S INDEX (PK_SENSOR), R SD INDEX (FK_SENSOR_DATA__SENSOR))))

i tested now and if i create stored procedure it honor plan but view not

Commented by: @livius2

For above situation with view i can provide whole metadata if you need it

Commented by: @dyemanov

It's not expected to work for views. In theory, it could, but only for trivial "select ... from view". Any extra condition added, or any table joined, or whatever else, would make the plan unusable hence ignored. Embedding the plan inside the view (even if syntactically allowed) is a design mistake, you shouldn't go this route.

Commented by: @livius2

Yes, i only use explicit plan in view for its main query only if i use it as
SELECT * FROM VIEW without any other conditions
with joining it will have no sense - maybe only as materialized wiew (same as stored proc with for select)

But do you think that something like raising error will be better than changing explicitly stored plan?

Commented by: @livius2

I have another sample

SELECT
COUNT(*)
FROM
UMOWA U5
LEFT JOIN ZLEC_FAKT ZF ON ZF.UMOWA_ID = U5.UMOWA_ID AND ZF.DYR_ID = U5.DYR_ID AND ZF.UMOWA_ID_SEQ = U5.UMOWA_ID_SEQ
WHERE
ZF.ZLEC_FAKT_STATUS_ID IN(1, 2, 3)
AND ZF.ZLEC_FAKT_RODZ_DOK_KSIEG_ID || '' = 'F'
AND U5.PRAC_ID = 16
AND U5.DYR_ID = 14
AND ZF.DATA_WYSTAWIENIA_FAKT >= CAST('2017-09-01' AS DATE)
AND ZF.DATA_WYSTAWIENIA_FAKT < CAST('2017-10-01' AS DATE)
AND ZF.NR_FAKT_KORYG = ''
PLAN JOIN (U5 INDEX (UMOWA_FK3), ZF INDEX (IXA_ZLEC_FAKT__NR_FAKT_KORYG, ZLEC_FAKT_FK4))

Firebird change plan to
PLAN JOIN (U5 INDEX (UMOWA_FK3), ZF INDEX (ZLEC_FAKT_FK4, IXA_ZLEC_FAKT__NR_FAKT_KORYG))<- changed index order

Commented by: @livius2

statistics of indexes for above
ZLEC_FAKT_FK4 is better from engine POV and it ignore user PLAN

IXA_ZLEC_FAKT__NR_FAKT_KORYG 0.000024 0 NR_FAKT_KORYG 0.000024
ZLEC_FAKT_FK4 0.000005 0 UMOWA_ID 0.000005
ZLEC_FAKT_FK4 0.000005 1 DYR_ID 0.000005
ZLEC_FAKT_FK4 0.000005 2 UMOWA_ID_SEQ 0.000005

Commented by: @dyemanov

Are you sure index order makes any difference?

Commented by: @livius2

I am not sure on Firebird
But Interbase use index in oposite direction and it run query fast
But i can not test if this change something in Firebird because plan is ignored by engine