Re: Why isn't an index scan being used?

On Wed, 20 Feb 2019 at 13:11, Abi Noda <a(at)abinoda(dot)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.

--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services