Re: Why does query planner choose slower BitmapAnd ?

Jeff Janes <jeff(dot)janes(at)gmail(dot)com> writes:
> I looked into this before as well, and I think it is vastly
> underestimating the cost of adding a bit into the bitmap, near this
> comment:

> /*
> * Charge a small amount per retrieved tuple to reflect the costs of
> * manipulating the bitmap. This is mostly to make sure that a bitmap
> * scan doesn't look to be the same cost as an indexscan to retrieve a
> * single tuple.
> */

> It charges 0.1 CPU_operator_cost, while reality seemed to be more like
> 6 CPU_operator_cost.

That sounds *awfully* high. I don't have any problem with the idea that
that number is off, but I'd want to see some evidence before bumping it
by a factor of 60.

The general assumption here is that most of the per-tuple costs ought to
be reflected in cpu_tuple_cost and cpu_index_tuple_cost. This addition is
meant to reflect the extra cost of going through a bitmap rather than
just fetching the tuple directly. That extra cost is certainly not zero,
but it seems to me that it ought to be fairly small relative to the other
processing costs of index and heap fetch. With the default cpu_xxx_costs,
what you suggest here would mean charging twice as much CPU per-tuple for
a bitmap scan as for a plain index scan, and that doesn't sound right.
(Or if it is right, maybe we have a performance bug in tidbitmap.c.)

[ thinks for a bit... ] Another thought to look into is that I don't
think the planner worries about the bitmap becoming "lossy", which would
result in many more heap tuple checks than it's predicting. It might be
that we need to model that effect. I don't think it's at play in Seamus'
example, given the large work_mem he's using, but maybe it explains your
results?

regards, tom lane