Re: Why does query planner choose slower BitmapAnd ?

Seamus Abshere <seamus@abshere.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