Hello all, I have a table that contains columns that describe an item. E.g.

ITEM table

  ID bigint not null

  COLOUR bigint  not null  (indexed)

  FINISH bigint (indexed)

Then another table that contains a qty of items that is reserved. I.e. the specification and qty of items reserved. Colour is mandatory and Finish is optional

RESERVATION table

  ID bigint not null

  COLOUR bigint  not null  (indexed)

  FINISH bigint (indexed)

  QTY integer not null

Now to determine available stock (item count - reservations) I issue the following query:

select

  STOCK.COLOUR,

  STOCK.FINISH,

  STOCK. STOCK_QTY,

  R.QTY as RESERVED_QTY,

  STOCK. STOCK_QTY - R.QTY as RESERVED_QTY as AVAILABLE_QTY

  from

   (

    /* Get summary of item stock */

   select COLOUR,FINISH,count(*) as STOCK_QTY

      from ITEM

     group by COLOUR,FINISH

) STOCK

left outer join RESERVATION R     /* Get Reservations */

  on R.COLOUR = STOCK.COLOUR  --  Match mandatory field

  and coalesce(R.FINISH,-1) = coalesce(STOCK.FINISH,-1)  --  Match optional field

The reason for using the coalesce in the join is to force NULL Finishes to match. The problem is that the query plan does not use the indices on FINISH because of the coalesce. This results in HUGE number of reads on the RESERVATION table. Think it table scans the RESERVATION table for each ITEM row. I tried a computed index, but the query did not use that index. The only (non FB) way to optimise the query is to actually refactor the tables and extract a SPECIFICATION table. In my case this will mean a HUGE amount of work and not feasible at this stage.

So my question is: How to optimise this query given that I use FB 2.5.3…?

Thanks