Marius,

> Is it possible to optimize the following select query?
>
> I make use of Firebird 2.5.2.26540 in Super Server mode.
>
> The query looks like this:
>
> SELECT
>   Sum(SALEITEMS.QUANTITY),
>   Sum(SALEITEMS.QUANTITY * SALEITEMS.COSTPRICEEX) FROM
>   SALES SALES
>   INNER JOIN SALEITEMS SALEITEMS ON SALES.LINECODE =
> SALEITEMS.LINECODE WHERE
>   SALES.POSTSTATUS = 'Posted' AND
>   SALES.SALE_DATE BETWEEN :vStartDate AND :vEndDate AND
>   SALEITEMS.SKU = :vSKU AND
>   SALES.CASHCREDIT = 'Cash'
>
> The plan being utilized is as follow: PLAN JOIN (SALEITEMS INDEX
> (I_SALEITEMS_SKU), SALES INDEX (I_SALES_ULINECODE))

I would suspect that the current indexes are too "broad" for the system to be able to narrow the criteria.

What is the selectivity/uniqueness of the POSTSTATUS, SALE_DATE and CASHCREDIT fields/columns?

Personally, I would be inclined to create a compound index on these three fields in the Sales table, where the order to the fields/columns in the index definition would go from least unique to most specific (ie. POSTSTATUS + CASHCREDIT + SALE_DATE or CASHCREDIT + POSTSTATUS + SALE_DATE).

That type of index would allow for the SALES entries to be narrowed quickly, leaving the SKU evaluation as a secondary/notional evaluation.


Sean

P.S. You might also want to create an compound index on SALESITEMS on SKU + LINE Code (in that order, from least to most unqiue). Depending on the number of Line Code entries per Sales that index could almost be considered a unique relationship for each SALES entry.