Re: Why does query planner choose slower BitmapAnd ?

Seamus Abshere <seamus(at)abshere(dot)net> writes:
> I don't understand why the query planner is choosing a BitmapAnd when an
> Index Scan followed by a filter is obviously better.

> On Postgres 9.4.4 with 244gb memory and SSDs

> maintenance_work_mem 1000000
> work_mem 500000
> random_page_cost 1
> seq_page_cost 2

[ squint... ] There's no physically explainable situation where
random_page_cost should be less than seq_page_cost. You may be
hitting a "garbage in, garbage out" situation with those numbers.

Given the large amount of RAM and the SSD underlying storage,
I'd set random_page_cost = seq_page_cost = 1. You might also
find it advantageous to increase the CPU cost parameters a touch.
I've heard it reported that setting cpu_tuple_cost to something like
0.03 to 0.05 provides a better fit to modern hardware than the
default setting does. In this particular case, though, it seems
like what you need to do is bump up cpu_index_tuple_cost a little
so as to make the indexscan on idx_houses_phoneable look more expensive.

(BTW, is that index really on just a boolean column? It seems
unlikely that "phoneable" would be a sufficiently selective
condition to justify having an index on it. I'd seriously consider
dropping that index as another solution approach.)

regards, tom lane