> > Without I_PBMCLAIM_DATESBM instead of scanning a few months of data
> > it's scanning a few years.
>
> Keep in mind and make sure, that:
>
> * You test in a fetch all rows scenario. While a certain index might make
> execution of a statement look fast when fetching the first bunch of rows
> automatically due to a client request, things may look different compared to
> non-indexed access when you fetch all records

A tip,

The easiest way I have found to measure the fetch all/total query execution is to enclose the candidate query in a SELECT COUNT(*) FROM () as in:

SELECT COUNT(*)
FROM (
{INSERT YOUR QUERY HERE}
)

The advantage of this approach is that the entire query is processed on the server and only a single row is returned.

Otherwise, as Thomas stated, you may end up measuring only the time it takes to return the first batch of rows, not the entire query -- which can have dramatically different performance profile (it may take 1s to process the first batch of rows, but 10m to process the next 10 -- due to the query construction/sub-queries).


Sean