On Tue, Feb 19, 2019, 8:00 PM Andrew Gierth <and...@tao11.riddles.org.uk wrote:
> >>>>> "Abi" == Abi Noda <a...@abinoda.com> writes: > > Abi> However, when I index the closed column, a bitmap scan is used > Abi> instead of an index scan, with slightly slower performance. Why > Abi> isn't an index scan being used, given that the exact same number > Abi> of rows are at play as in my query on the state column? > > Most likely difference is the correlation estimate for the conditions. > The cost of an index scan includes a factor based on how well correlated > the physical position of rows is with the index order, because this > affects the number of random seeks in the scan. But for nulls this > estimate cannot be performed, and bitmapscan is cheaper than plain > indexscan on poorly correlated data. > Does this imply that the optimizer would always prefer the bitmapscan rather than index scan even if random page cost = 1, aka sequential cost, when the correlation is unknown like a null? Or only when it thinks random access is more expensive by some significant factor? > -- > Andrew (irc:RhodiumToad) > >
