Partial index using ranges not been selected in plan

[todaysoftware]

In Firebird 5.0.1 when using a partial index with various range selections it always seems to fall back to a table scan (PLAN NATURAL)

CREATE INDEX AUDIT_LINETYPE ON AUDIT(LINETYPE) WHERE LINETYPE > 0 AND LINETYPE < 32767;

SELECT * FROM AUDIT WHERE LINETYPE = 42;
Select Expression
    -> Filter
        -> Table "AUDIT" Full Scan
(i.e. PLAN (AUDIT NATURAL))

Tried various other options such as LINETYPE > 0, LINETYPE IN (40,41,42,43,44,45,46,47), LINETYPE BETWEEN 40 AND 47, all of which result in a table scan, But the following does work (and technically is probably equivalent to the IN ( ) above) LINETYPE = 40 OR LINETYPE = 41 OR LINETYPE = 42 OR ... OR LINETYPE = 47

SELECT TranNo FROM Audit  WHERE  LINETYPE = 42;
Select Expression
    -> Filter
        -> Table "AUDIT" Access By ID
            -> Bitmap
                -> Index "AUDIT_LINETYPE" Range Scan (full match)

But is is pretty inconvenient since there is probably a couple of dozen line types that should be included in the partial index so using a range or IN would be much better. The condition mentioned at the top would be OK since in our case around 3/4 of the values in the index would be either 0 or 32767, but probably ideally something like the following would be slightly better to exclude some additional values we are not interested in.

(LINETYPE BETWEEN 1 AND 7999) OR (LINETYPE BETWEEN 8100 AND 32766)

(possibly related to #7804 )

[dyemanov]

The optimizer cannot detect that value 42 matches the index range, as expressions are not evaluated at compile time. You need to specify that explicitly:

SELECT * FROM AUDIT WHERE LINETYPE > 0 AND LINETYPE < 32767 AND LINETYPE = 42;

This may look cumbersome but should work.

[todaysoftware]

Thanks for the reply :+1:. I have modified the filter on the index to use OR instead (a couple of dozen conditions covers most of our use cases fine). After doing this we get the following results

SHOW INDEX AUDIT_LINETYPE;
AUDIT_LINETYPE INDEX ON AUDIT(LINETYPE)
 WHERE LINETYPE=2 OR LINETYPE=4 OR ... LINETYPE=40 OR LINETYPE=41 OR LINETYPE=42 OR LINETYPE=43 OR ... LINETYPE=50

SELECT * FROM AUDIT WHERE LINETYPE = 42;
Select Expression
    -> Filter
        -> Table "AUDIT" Access By ID
            -> Bitmap
                -> Index "AUDIT_LINETYPE" Range Scan (full match)

SELECT * FROM AUDIT WHERE LINETYPE IN (43);
Select Expression
    -> Filter
        -> Table "AUDIT" Access By ID
            -> Bitmap
                -> Index "AUDIT_LINETYPE" Range Scan (full match)

SELECT * FROM AUDIT WHERE LINETYPE IN (42,43);
Select Expression
    -> Filter
        -> Table "AUDIT" Full Scan

SELECT * FROM AUDIT WHERE (LINETYPE = 42 OR LINETYPE = 43);
Select Expression
    -> Filter
        -> Table "AUDIT" Access By ID
            -> Bitmap Or
                -> Bitmap
                    -> Index "AUDIT_LINETYPE" Range Scan (full match)
                -> Bitmap
                    -> Index "AUDIT_LINETYPE" Range Scan (full match)

So the outlier there is the IN (42,43) however changing that to an OR works. I do feel that they should get the same result (i.e. both use the index) so maybe something to look at. I had a feeling a read somewhere that the parser/optimizer actually turns IN( ) into OR anyway so they should end up being equivalent. (but this inconsistency is something we can deal with although having it work consistently sometime in the future would be nice)

[dyemanov]

Starting with v5, IN is not decomposed into ORs but instead is processed natively. That said, partial index matching could be improved to handle your case (it was already noted in #7804).

[livius2]

The optimizer cannot detect that value 42 matches the index range, as expressions are not evaluated at compile time. You need to specify that explicitly:

SELECT * FROM AUDIT WHERE LINETYPE > 0 AND LINETYPE < 32767 AND LINETYPE = 42;

This may look cumbersome but should work.

Can you explain it more? I don't understand this. Is the partial index implemented as an expression index, and does the full expression need to be specified in the query?

[dyemanov]

It's implemented differently (index keys are not based on expression), but from the optimizer POV they're similar - query expression must be matched to the index expression. The difference is that for expression indices they must be an exact match, while for partial indices their usage may be derived from different expressions. The rules are documented in the chapter "5.5.1.3. Partial Indexes" of the v5 Language Reference. There may be more rules to deduce the index usage possibilities in future FB versions.

[todaysoftware]

Thanks for the information and the documentation was definitely helpful (I did actually find it a week or two ago as well). So understand much better the current way it is working and have adjusted by using OR on a dozen or so values, and that works pretty well for the particular use case we had