>I've noted that the documentation says that whether your index is ASC or
>DESC matters, but it's not clear to me either why it should or exactly
>what the implications are.
>
>Boiled down[#], I've got a table MYTABLE with an integer column MYCOLUMN
>with an index on it, and I'm looking at queries like
>
>(a) select COUNT(*) from MYTABLE where MYCOLUMN < 2
>(b) select COUNT(*) from MYTABLE where MYCOLUMN > 2
>
>The vast majority of rows in the table have MYCOLUMN = 2, so I'm looking
>at these queries to return very small numbers very quickly. (MYCOLUMN =
>2 means "this object is OK", and I'm looking to find the objects that
>are not OK.)
>
>If the index is ASC then
>
>(a) is fast
>(b) is very slow (although it claims to be using the index, according to
>the plan, it's doing as much work as a table scan, according to the stats)

Decided that a value in the middle should mean OK, is - well - not the greatest idea. Probably you also started out with a limited number of options and then adding other values to have other meanings made sense once upon a time. I would recommend to change OK from being 2 to being something like 9999 (a higher value than you expect to need for other purposes). If that's not easy, I would consider adding the column MyColumnNegative, populated through a trigger and set to the negative equivalent of MyColumn. Then a) would still be fast, and if you changed b) to

(b) select COUNT(*) from MYTABLE where MYCOLUMNNEGATIVE < -2

then that should also be fast.

Other options that you could consider at least include using computed indexes (though I've never tried them) and adding a triggerpopulated new table(s) - either containing references to records that are not OK or a table containing the sums (when records are deleted, you insert a row containing -1, when records are inserted, you insert a row containing +1, when a row is modified, you may end up inserting one row with +1 and another with -1, occationally you sum data and delete old sums).

All in all, you have quite a few options you can consider to circumvent your problem while awaiting Firebird 3 with histograms (haven't checked alpha 1 yet, so I'm not certain they are included).

HTH,
Set