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