Robert,

> >>> What indexes are defined on the BranchSend table?
> >>>
> >> CREATE INDEX BranchSend_RegionRef ON BranchSend(RegionRef)^
> >> CREATE INDEX BranchSend_SendTable ON BranchSend(SendTable)^
> >> CREATE INDEX BranchSend_PkRef ON BranchSend(PkRef)^
> >> CREATE INDEX BranchSend_TransConf ON BranchSend(TransferConfirmed)^
> >> CREATE INDEX BranchSend_TransferBatchRef ON
> >>
> > BranchSend(TransferBatchRef)^
> >
> > How many unique RegionRef values are there?
>
> = to the number of regions (branches) currently 15.
>
> > How many unique SendTable values are there?
>
> About 10
>
> > What is the exact SQL and PLAN generated for the counts query?
> >
>
> SQL
>
> SELECT bs.SendTable, COUNT(bs.SendTable) AS NumRecs
> FROM BranchSend bs
> WHERE bs.RegionRef = :RegionRef
> AND bs.TransferConfirmed = 'F'
> GROUP BY bs.SendTable
>
> PLAN
>
> PLAN (BS ORDER BRANCHSEND_SENDTABLE INDEX (BRANCHSEND_REGIONREF,
> BranchSend_TransConf))

The fact that the engine is joining 2 indexes which have a relatively
low selectivity is not good. In fact, it wouldn't surprise me if it was
the major reason for the query run time.

Based on these details and the SQL, I would suggest that you try:

- Change the SQL to read: ...AND bs.TransferConfirmed||'' = 'F'...

- try the following compound index with the original query:

CREATE INDEX BranchSend_RegionCounts ON BranchSend(TransferConfirmed,
RegionRef, SendTable)



Sean