Re: Are bitmap index scans slow to start?

Hi Jeff, thanks for the reply.

<<

What is going on during the interregnum? Whatever it is, it seems to be
driving the log_2013_01_session_idx index out of the cache, but not the
log_2013_01 table. (Or perhaps the table visit is getting the benefit of
effective_io_concurrency?)
.

Rebuilding the index might help, as it would put all the leaf pages holding
values for session_id=27 adjacent to each other, so they would read from
disk faster. But with a name like "session_id", I don't know how long such
clustering would last though.

>>

Technically, nothing should be happening. We used to keep one massive audit
log, and was impossible to manage due to its size. We then changed to a
strategy where every month a new audit log would be spawned, and since
log_2013_01 represents January, the log should be closed and nothing should
have changed (it is technically possible that a long-running process would
spill over into February, but not by this much). So, assuming that it's
stable, it should be a very good candidate for reindexing, no?

Our effective_io_concurrency is 1, and last I heard the PG host was a LINUX
4 drive RAID10, so I don't know if there is any benefit to raising this
number - and if there was any benfit, it would be to the Bitmap Scan, and
the problem is the data building before the fact.

>> the bitmap itself doesn't get cached. But the data needed to construct
the bitmap does get cached. It gets cached by the generic caching methods
of PG and the OS, not through something specific to bitmaps.
<<

This has always been a problem for me. I spend hours trying different
strategies and think I've solved the problem, when in fact it seems like a
cache has spun up, and then something else expires it and the problem is
back. Is there a way around this problem, can I force the expiration of a
cache?

Carlo