> Most of this likely boils down to random_page_cost being a guess. You may want to check your effective_cache_size is set to something like 75% of the machine's memory, and/or tweak random page cost down, if it's set to the standard 4 setting.
On Wed, 20 Feb 2019 at 13:11, Abi Noda <a@abinoda.com> wrote: > However, when I index the closed column, a bitmap scan is used instead of an index scan, with slightly slower performance. Why isn't an index scan being used, given that the exact same number of rows are at play as in my query on the state column?
That's down to the planner's cost estimates. Likely it thinks that either doing a bitmap scan is cheaper, or close enough that it does not matter.
> How do I index closed in a way where an index scan is used?
The costing does account for the size of the index. If the "closed_index" index is large than the "state_index", then doing an Index scan on "closed_index" is going to be costed higher.
Most of this likely boils down to random_page_cost being a guess. You may want to check your effective_cache_size is set to something like 75% of the machine's memory, and/or tweak random page cost down, if it's set to the standard 4 setting. modern SSDs are pretty fast at random reads. HDDs, not so much.
