Re: Are bitmap index scans slow to start?

On Wed, Feb 27, 2013 at 1:38 PM, Carlo Stonebanks <
stonec(dot)register(at)sympatico(dot)ca> wrote:

>
>
> >> Is the original query you posted part of the transform process, rather
> than being the production query you run after the ETL is over?
>
> ****
>
> Neither, it is part of our auditing and maintenance processes. It is not
> called with any great frequency. The audit report generates rows defining
> how the a particular item (an “item” being a particular table/row) was
> created: it returns the names of the import tables, the row ids, the write
> operations and any transformation messages that may have been generated –
> all in the order they occurred.****
>
> ** **
>

...

> **
>
> So, this query is not called often, but the fact is that if it takes over
> 30 seconds to load an item (because the audit report takes so long to
> prepare the bitmap index scan when passed new query parameters) then it
> severely restricts how much data we can resurrect at any one time.****
>

Is that a restriction you have observed, or are you extrapolating based on
a single query? If you run a bunch of similar queries in close succession,
it is likely that the first few queries will warm up the cache, and
following queries will then run much faster. Also, if you restructure the
series of queries into a large one that reconstructs many rows
simultaneously, it might choose a more efficient path than if it is fed the
queries one at a time.

Cheers,

Jeff