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