Re: two index bitmap scan of a big table & hash_seq_search

"Sergey E. Koposov" <math(at)sai(dot)msu(dot)ru> writes:
> But the funny thing I noticed is that the query after running a certain
> amount of time doing I/O, starts to use 100%CPU and spend 99% the time in
> hash_seq_search. Here is the oprofile of PG during that period:
> --------
> CPU: Intel Core/i7, speed 2.268e+06 MHz (estimated)
> Counted CPU_CLK_UNHALTED events (Clock cycles when not halted) with a unit mask of 0x00 (No unit mask) count 100000
> samples % symbol name
> 303404 99.3562 hash_seq_search
> 1163 0.3808 tbm_lossify
> 639 0.2093 hash_search_with_hash_value

It seems like you've uncovered a scaling limitation in the tidbitmap
logic when it has to deal with very very large numbers of pages.

I might be reading too much into the mention of tbm_lossify, but
I wonder if the problem is repeated invocations of tbm_lossify()
as the bitmap gets larger. Maybe that function needs to be more
aggressive about how much information it deletes per call.

regards, tom lane