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

On Fri, 19 Aug 2011, Tom Lane wrote:
> 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.
Thanks for idea, Tom.

Yes, it turns out that the problem was in lossify'ing the bitmap to
intensely. I've put the elogs around the lossification in tbm_add_tuples()
if (tbm->nentries > tbm->maxentries)
{
elog(WARNING, "lossifying %d %d", tbm->nentries,
tbm->maxentries);
tbm_lossify(tbm);
elog(WARNING, "lossified %d", tbm->nentries);
}

And I saw in my log
koposov:wsdb:2011-08-20 17:31:46 BST:21524 WARNING: lossifying 13421773 13421772
koposov:wsdb:2011-08-20 17:31:46 BST:21524 WARNING: lossified 13421772
issued with a rate of 20000 per second. E.g. it lossifies one page per
lossify call (and does a lot of hash_seq_search operations too) ...

After that I changed the check in tbm_lossify()
from:
if (tbm->nentries <= tbm->maxentries)
to:
if (tbm->nentries <= (0.8*tbm->maxentries))

which allowed the query finish in 75 seconds (comparing to 3hours).

I'm not entirely sure that my fix of the tbm_lossify function is a proper
one, but it looks all right.
Do you think that this should be fixed ?

Sergey

*******************************************************************
Sergey E. Koposov, PhD
Institute for Astronomy, Cambridge/Sternberg Astronomical Institute
Web: http://lnfm1.sai.msu.ru/~math
E-mail: math(at)sai(dot)msu(dot)ru