Tom, all,
* Tom Lane (tgl@sss.pgh.pa.us) wrote:
> 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.
Certainly agree with Tom on the above point.
> (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.)
Also agreed here, but I've seen field evidence (with reasonable
configurations) that definitely shows that we're a bit too happy to go
with a BitmapAnd scan across two indexes where one returns an order of
magnitude (or less) pages to consider than the other and most of the
time we spend on the overall query is in going through the index to find
a bunch of pages we're just going to throw away when we do the AND.
In one specific case which I can recall offhand (having seen it quite
recently), there was a btree index and a gist index (PostGIS geometry)
where the btree index pulled back perhaps 100k rows but the gist index
returned nearly everything (the bounding box included in the query
covering almost the entire table). Dropping the gist index greatly
improved *that* query, but, of course, destroyed the performance of more
selective queries bounding box queries which didn't include a constraint
on the column with the btree index (forcing a sequential scan of the
table).
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?
Thanks!
Stephen