Jie Zhang wrote:
>
> On 7/24/06 6:59 AM, "Hannu Krosing" <hannu@skype.net> wrote:
>
>>
>>
>> And also for AND-s of several indexes, where indexes are BIG, your btree
>> indexes may be almost as big as tables but the resulting set of pages is
>> small.
>
> Yeah, Hannu points it out very well -- the bitmap index works very well when
> columns have low cardinalities and AND operations will produce small number
> of results.
>
> Also, the bitmap index is very small in low cardinality cases, where the
> btree tends to take up at least 10 times more space.
>
>
The smallness of the bitmap index also means that some queries will
require much less work_mem to achieve good performance e.g consider:
TPCH dataset with scale factor 10 on my usual PIII HW, query - select count(*) from lineitem where l_linenumber=1;
This executes in about 100 seconds with work_mem = 20M if there is a
bitmap index on l_linenumber. It takes 3832 seconds (!) if there is a
btree index on the same column. Obviously cranking up work_mem will even
up the difference (200M gets the btree to about 110 seconds), but being
able to get good performance with less memory is a good thing!
Cheers
Mark