Are bitmap index scans slow to start?

On Friday, February 22, 2013, Carlo Stonebanks wrote:

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

Yes, assuming the problem is reading the index data from disk, that sounds
like a good candidate for reindexing (and maybe clustering as well).

> ****
>
> ** **
>
> 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?
>
You can clear the PG cache by restarting the instance. To clear the OS
cache as well you can do this (Linux)

<stop postgres>
sync
sudo echo 3 > /proc/sys/vm/drop_caches
<start postgres>

But I think it would be better just not to execute the same query
repeatedly. For example, each time you execute it during testing, pick a
different session_id rather than using 27 repeatedly. (It might also be a
good idea to change up the hard-coded in-list values you have, but with the
plans you are currently seeing that isn't important as those are being used
in a filter not a look-up)

Cheers,

Jeff