hi, i write this on devel - because only developers can answer if this change is intended query like: SELECT 1 FROM SPRAWOZDANIE_POZ SP2 INNER JOIN ZLEC_FAKT ZF ON SP2.DYR_ID = ZF.DYR_ID AND SP2.INSP_ID = ZF.INSP_ID AND SP2.POZ_ZLEC_FAKT_ROK = ZF.ZLEC_FAKT_ROK AND SP2.POZ_ZLEC_FAKT_NR = ZF.ZLEC_FAKT_NR WHERE SP2.UMOWA_ID = '63040080' AND SP2.DYR_ID = 9 AND SP2.UMOWA_ID_SEQ = 0 AND SP2.UMOWA_ID_POZ = 1 AND SP2.SPR_ID = 5978 GROUP BY SP2.DYR_ID , SP2.INSP_ID , SP2.POZ_ZLEC_FAKT_ROK , SP2.POZ_ZLEC_FAKT_NR all indexes are composite but all parts are numeral like Integer all index statistics are valid (database restored from backup in both servers) ------------------------------------------------------------------------------------------------------------------------------------------------- plan in FB2.5 PLAN JOIN (SP2 ORDER FK_SPRAWOZDANIE_POZ__ZF_POZ INDEX (FK_SPRAWOZDANIE_POZ__SPR), ZF INDEX (ZLEC_FAKT_PK)) Executing statement... Statement executed (elapsed time: 0.000s). 135 fetches, 0 marks, 0 reads, 0 writes. 0 inserts, 0 updates, 0 deletes, 51 index, 0 seq. Delta memory: 32392 bytes. Total execution time: 0.031s Script execution finished. ------------------------------------------------------------------------------------------------------------------------------------------------- plan in FB3 (current snapshot WI-V3.0.1.32570 Firebird 3.0) PLAN JOIN (SP2 ORDER FK_SPRAWOZDANIE_POZ__ZLEC_FAKT, ZF INDEX (ZLEC_FAKT_PK)) Executing statement... Statement executed (elapsed time: 0.000s). 103908 fetches, 0 marks, 0 reads, 0 writes. 0 inserts, 0 updates, 0 deletes, 34635 index, 0 seq. Delta memory: -376 bytes. Total execution time: 0.094s Script execution finished. ------------------------------------------------------------------------------------------------------------------------------------------------- FK_SPRAWOZDANIE_POZ__ZLEC_FAKT ASC DYR_ID, INSP_ID, POZ_ZLEC_FAKT_ROK, POZ_ZLEC_FAKT_NR selectivity 0.000032 PK_SPRAWOZDANIE_POZ ASC POZ_ID, DYR_ID selectivity 0.000003 FK_SPRAWOZDANIE_POZ__SPR ASC SPR_ID, DYR_ID selectivity 0.000024 ------------------------------------------------------------------------------------------------------------------------------------------------- this query is subquery on main query and slow down >6 times for us we can of course write explicit plan and other tricks but - is this change in query plan intended? What more infos do you need? regards, Karol Bieniaszewski
------------------------------------------------------------------------------
Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
