I'm surprised that the index isn't picked up. Though I normally use
single field indexes myself, it should be able to pick up the first half
of the index in your case.

I suppose you could try

SELECT SUM(INVOICETOTAL) FROM INVOICES
WHERE CUSTOMER=123 AND INVOICETOTAL+0 <> 0;

just to see if it helps making the optimizer less confused.

If this helps, I'd say Firebird 1.5.3 has a bug, if it doesn't, I'd
suspect that your index isn't very selective or that the table contains
very few rows (test whether SELECT SUM(INVOICETOTAL) FROM INVOICES WHERE
CUSTOMER=123 uses the index).

Another thing that is known to confuse Firebird, is if you have several
indexes that are basically identical, do you have other indexes,
constraints or keys that starts with the CUSTOMER field?

HTH,
Set

dr_bentonquest wrote:

> Hi there,
>
> I have an INVOICES table like this:
>
> create table INVOICES
> (
> NUMBER integer,
> CUSTOMER integer,
> INVOICETOTAL numeric(10,2),
> ...more fields...
> )
>
> I set up an index like this:
>
> CREATE INDEX INV_TOTAL on INVOICES(CUSTOMER,INVOICETOTAL);
>
> Now when executing the following query, the index is picked up
> correctly in the plan:
>
> SELECT SUM(INVOICETOTAL) FROM INVOICES
> WHERE CUSTOMER=123 AND INVOICETOTAL > 0;
>
> However, for the following queries the index is NOT picked up:
>
> SELECT SUM(INVOICETOTAL) FROM INVOICES
> WHERE CUSTOMER=123 AND INVOICETOTAL <> 0;
>
> SELECT SUM(INVOICETOTAL) FROM INVOICES
> WHERE (CUSTOMER=123) AND (NOT INVOICETOTAL = 0);
>
> I'm currently using FB 1.5.3. Is there any workaround I could use to
> solve this? I need the total for invoices where the INVOICETOTAL field
> is not zero.
>
> Thanks in advance,
>
> -Benton