> > 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
