> The following query needs about 10 seconds to execute on a table with around 7 Mio. lines:
>
> select * from lin_albaran_venta where nro_factura = '0000001' order by nro_albaran, nro_linea;
> Plan:
> PLAN (LIN_ALBARAN_VENTA ORDER PK_LIN_ALBARAN_VENTAPK INDEX (FK_LIN_ALBARAN_VENTA_13))
>
> When adding field nro_factura to the order by, the query speeds up to 0,2 secs:
>
> select * from lin_albaran_venta where nro_factura = '0000001' order by nro_factura, nro_albaran, nro_linea;
> Plan:
> PLAN SORT ((LIN_ALBARAN_VENTA INDEX (FK_LIN_ALBARAN_VENTA_13)))
>
> nro_factura is a FK, and (nro_albaran, nro_linea) the PK of table LIN_ALBARAN_VENTA.
>
> We recently upgraded from FB 2.1 to 2.5, and on 2.1 we didn't run into this problem.
>
> Is there any solution to this, perhaps in the configuration file, or do we have to revise
> all the order by's in our application, or perhaps even go back to FB 2.1?

Hi Stefan!

With every new Firebird version, the optimizer is improved. In most cases, that will lead to quicker or equally quick query execution, but occasionally the optimizer gets things wrong and things slow down. I have little to no experience with Fb 2.5, so I cannot say how often you risk running into such a problem, just that regarding Fb 2.5 vs Fb 2.1 this is not commonly reported to this list.

The quick solution would be the old +0 or || '' trick, i.e. change your query to

order by nro_albaran+0, nro_linea;

or

order by nro_albaran || '', nro_linea;

depending on the type of nro_albaran. Adding this to the first field in the order by, should at least prevent any index from being used for the ordering part (although, in some situations using the order by could speed things up).

I don't think you'll find anything in any configuration file, and, yes, it could be beneficial to test your queries when changing database version (you don't have to execute all queries, just take a closer look at those that produce a different plan when comparing Fb 2.1 to Fb 2.5).

HTH,
Set