Re: Why does query planner choose slower BitmapAnd ?

Stephen Frost <sfrost(at)snowman(dot)net> writes:
> I've not looked into the specific costing here to see why the BitmapAnd
> ended up being chosen over just doing an index scan with the btree and
> then filtering, but I do believe it to be a problem area that would be
> good to try and improve. The first question is probably- are we
> properly accounting for the cost of scanning the index vs the cost of
> scanning one index and then applying the filter?

We are costing it out in what seems a sane way to me. In the given
example the "bad" plan is estimated at just slightly cheaper than what
(I assume) the "good" plan is. I'm inclined to think this represents a
failure to choose good cost parameters for the installation.

Given how remarkably quick the single-index scan is, I also wonder if
that index is fully cached while we had to read some of the other index
from kernel or SSD. Relative cache states of different indexes is a
problem the planner doesn't currently try to deal with; it's possible
that that could bias it towards trying to AND a large-but-not-fully-cached
index with a smaller-and-fully-cached-one, when not bothering with the
larger index would in fact be better. You might be able to counter that
to some extent by reducing effective_cache_size, though possibly that
cure is worse than the disease.

regards, tom lane