Tom, all, * Tom Lane (tgl@xxxxxxxxxxxxx) wrote: > Seamus Abshere <seamus@xxxxxxxxxxx> 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
Attachment:
signature.asc
Description: Digital signature
