Re: Why isn't an index scan being used? - Mailing list pgsql-performance

From Andrew Gierth
Subject Re: Why isn't an index scan being used?
Date
Msg-id 87ftsji2tb.fsf@news-spur.riddles.org.uk
Whole thread Raw
In response to Why isn't an index scan being used?  (Abi Noda <a@abinoda.com>)
Responses Re: Why isn't an index scan being used?  (Michael Lewis <mlewis@entrata.com>)
List pgsql-performance
>>>>> "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.

-- 
Andrew (irc:RhodiumToad)


pgsql-performance by date:

Previous
From: Abi Noda
Date:
Subject: Re: Why isn't an index scan being used?
Next
From: Michael Lewis
Date:
Subject: Re: Why isn't an index scan being used?