Seamus Abshere <sea...@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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
