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

"Sergey E. Koposov" <math@sai.msu.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