Index not used unless ORDER BY clause is present (Firebird 5)

[Google Developer Account]

Hello Firebird community,
I'm encountering a behavior in Firebird 5 that I can't fully explain and would appreciate your insights.
I have a table with an index on a specific column. When I run a query with a WHERE clause filtering on that column, the index is not used. However, if I add an ORDER BY on the same column, the index is used.

Example:
-- Index is not used:
select first 1 belegnr
from belegpos
where belegtyp = 'V' and belegart = 'AU' and ekkontrakte = :icharge
order BY belegnr desc
into :fvau;

-- Index is used:

select first 1 belegnr
from belegpos
where belegtyp = 'V' and belegart = 'AU' and ekkontrakte = :icharge
order BY belegtyp, belegart, belegnr desc
into :fvau;

This SQL is part of a stored procedure together with 10 other SQLs like this one. This stored procedure is called four times in an other sql via Sub-Select. I know not optimal.

I’ve checked the plan with SET PLAN ON, and the difference is clearly visible.

What makes this more confusing: In Firebird 2.5, with the same data and the exact same SQL, the index is used even without the ORDER BY clause. This change in behavior between versions makes me wonder whether something changed in the optimizer logic.

Things I’ve already verified:
- The index exists and is active.
- The data types match exactly.

  - belegnr is integer

  - belegtyp is char(1) and indexed

  - belegart is varchar(5) and indexed

  - ekkontrakte is integer and indexed (compound index: ekkontrakte and vkkontrakte)
- No functions are applied to the column in the WHERE clause.
- The column is not part of a compound index (it's a single-column index).

I understand that Firebird’s optimizer may skip the index if it believes a natural scan would be faster, but I’m surprised that adding an ORDER BY causes the index to be used even though the filtering condition is exactly the same.

Is this expected behavior in Firebird 5, or am I missing something in how index selection works?

Thanks in advance for your help!

Best regards,
Leon Schwandt

[Dmitry Yemanov]

Hi,

> I'm encountering a behavior in Firebird 5 that I can't fully explain and
> would appreciate your insights.
> I have a table with an index on a specific column. When I run a query
> with a WHERE clause filtering on that column, the index is not used.
> However, if I add an ORDER BY on the same column, the index is used.

What "specific column" are you talking about, belegtyp or belegart or both?

> Example:
> -- Index is not used:
> select first 1 belegnr
> from belegpos
> where belegtyp = 'V' and belegart = 'AU' and ekkontrakte = :icharge
> order BY belegnr desc
> into :fvau;
>
> -- Index is used:
> select first 1 belegnr
> from belegpos
> where belegtyp = 'V' and belegart = 'AU' and ekkontrakte = :icharge
> order BY belegtyp, belegart, belegnr desc
> into :fvau;
>
> This SQL is part of a stored procedure together with 10 other SQLs like
> this one. This stored procedure is called four times in an other sql via
> Sub-Select. I know not optimal.
>
> I’ve checked the plan with SET PLAN ON, and the difference is clearly
> visible.

Please show the plans.


Dmitry

[Elmar Haneke]

Example:
-- Index is not used:
select first 1 belegnr
from belegpos
where belegtyp = 'V' and belegart = 'AU' and ekkontrakte = :icharge
order BY belegnr desc
into :fvau;

I would assume that your index on "belegtyp" and "belegart" are not very selective - they do have only a small number of different values over all rows.

For optimal performance of that statement you should have a compound index on "belegtyp", "belegart" and "ekkontrakte".

[Google Developer Account]

BELEGTYP = 3 distinct values
BELEGART = 15 distinct values
EKKONTRAKTE = 33044 distinct values

CREATE INDEX xxx ON belegpos (belegtyp, belegart, ekkontrakte); -- does not help, same execution time

Firebird 2.5:
PLAN SORT ((BELEGPOS INDEX (BP_HUR_KONTRAKTE)))SORT ((BELEGPOS INDEX (BP_HUR_KONTRAKTE)))SORT ((BELEGPOS INDEX (BP_HUR_KONTRAKTE)))SORT ((BELEGPOS INDEX (BP_HUR_KONTRAKTE)))SORT ((BELEGPOS INDEX (BP_HUR_KONTRAKTE)))(BELEG INDEX (BE_BELEG))SORT ((BELEGPOS INDEX (BP_HUR_KONTRAKTE)))(BELEG INDEX (BE_BELEG))SORT ((BELEGPOS INDEX (BP_HUR_KONTRAKTE)))(BELEG INDEX (BE_BELEG))(BELEG INDEX (BE_BELEG))SORT ((BELEGPOS INDEX (BP_HUR_KONTRAKTE)))
SORT
BELEGPOS  INDEX (BP_HUR_KONTRAKTE( EKKONTRAKTE[0,000087], VKKONTRAKTE[0,000074] ))
PLAN SORT ((BELEGPOS INDEX (BP_HUR_KONTRAKTE)))SORT ((BELEGPOS INDEX (BP_HUR_KONTRAKTE)))SORT ((BELEGPOS INDEX (BP_HUR_KONTRAKTE)))SORT ((BELEGPOS INDEX (BP_HUR_KONTRAKTE)))SORT ((BELEGPOS INDEX (BP_HUR_KONTRAKTE)))(BELEG INDEX (BE_BELEG))SORT ((BELEGPOS INDEX (BP_HUR_KONTRAKTE)))(BELEG INDEX (BE_BELEG))SORT ((BELEGPOS INDEX (BP_HUR_KONTRAKTE)))(BELEG INDEX (BE_BELEG))(BELEG INDEX (BE_BELEG))SORT ((BELEGPOS INDEX (BP_HUR_KONTRAKTE)))
SORT
BELEGPOS  INDEX (BP_HUR_KONTRAKTE( EKKONTRAKTE[0,000087], VKKONTRAKTE[0,000074] ))
PLAN SORT ((BELEGPOS INDEX (BP_HUR_KONTRAKTE)))SORT ((BELEGPOS INDEX (BP_HUR_KONTRAKTE)))SORT ((BELEGPOS INDEX (BP_HUR_KONTRAKTE)))SORT ((BELEGPOS INDEX (BP_HUR_KONTRAKTE)))SORT ((BELEGPOS INDEX (BP_HUR_KONTRAKTE)))(BELEG INDEX (BE_BELEG))SORT ((BELEGPOS INDEX (BP_HUR_KONTRAKTE)))(BELEG INDEX (BE_BELEG))SORT ((BELEGPOS INDEX (BP_HUR_KONTRAKTE)))(BELEG INDEX (BE_BELEG))(BELEG INDEX (BE_BELEG))SORT ((BELEGPOS INDEX (BP_HUR_KONTRAKTE)))
SORT
BELEGPOS  INDEX (BP_HUR_KONTRAKTE( EKKONTRAKTE[0,000087], VKKONTRAKTE[0,000074] ))
PLAN SORT ((BELEGPOS INDEX (BP_HUR_KONTRAKTE)))SORT ((BELEGPOS INDEX (BP_HUR_KONTRAKTE)))SORT ((BELEGPOS INDEX (BP_HUR_KONTRAKTE)))SORT ((BELEGPOS INDEX (BP_HUR_KONTRAKTE)))SORT ((BELEGPOS INDEX (BP_HUR_KONTRAKTE)))(BELEG INDEX (BE_BELEG))SORT ((BELEGPOS INDEX (BP_HUR_KONTRAKTE)))(BELEG INDEX (BE_BELEG))SORT ((BELEGPOS INDEX (BP_HUR_KONTRAKTE)))(BELEG INDEX (BE_BELEG))(BELEG INDEX (BE_BELEG))SORT ((BELEGPOS INDEX (BP_HUR_KONTRAKTE)))
SORT
BELEGPOS  INDEX (BP_HUR_KONTRAKTE( EKKONTRAKTE[0,000087], VKKONTRAKTE[0,000074] ))
PLAN SORT (JOIN (C NATURAL, K INDEX (HUR_KON_NR)))
SORT
JOIN
C  NATURAL
K  INDEX (HUR_KON_NR( KONTRAKTTYP[0,500000], STATUS[0,500000], KONTRAKTNR[0,000063] ))

Firebird 5:
PLAN (GDI_HUR_STATUSCHARGE NATURAL)
GDI_HUR_STATUSCHARGE  NATURAL
PLAN (GDI_HUR_STATUSCHARGE NATURAL)
GDI_HUR_STATUSCHARGE  NATURAL
PLAN (GDI_HUR_STATUSCHARGE NATURAL)
GDI_HUR_STATUSCHARGE  NATURAL
PLAN (GDI_HUR_STATUSCHARGE NATURAL)
GDI_HUR_STATUSCHARGE  NATURAL
PLAN SORT (JOIN (K INDEX (HUR_KON_NR), C INDEX (HUR_CHKON_KONNR)))
SORT
JOIN
K  INDEX (HUR_KON_NR( KONTRAKTTYP[0,500000], STATUS[0,500000], KONTRAKTNR[0,000063] ))

[Google Developer Account]

Is there any important information missing?

elmar schrieb am Freitag, 18. Juli 2025 um 09:50:43 UTC+2: