Stephen Frost <sfrost@xxxxxxxxxxx> writes: > 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? We are costing it out in what seems a sane way to me. In the given example the "bad" plan is estimated at just slightly cheaper than what (I assume) the "good" plan is. I'm inclined to think this represents a failure to choose good cost parameters for the installation. Given how remarkably quick the single-index scan is, I also wonder if that index is fully cached while we had to read some of the other index from kernel or SSD. Relative cache states of different indexes is a problem the planner doesn't currently try to deal with; it's possible that that could bias it towards trying to AND a large-but-not-fully-cached index with a smaller-and-fully-cached-one, when not bothering with the larger index would in fact be better. You might be able to counter that to some extent by reducing effective_cache_size, though possibly that cure is worse than the disease. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
