From: Andrew Gierth <andrew@tao11.riddles.org.uk>
To: Abi Noda <a@abinoda.com>
Cc: pgsql-performance@lists.postgresql.org
Subject: Re: Why isn't an index scan being used?
Date: Wed, 20 Feb 2019 03:00:23 +0000
Message-ID: <87ftsji2tb.fsf@news-spur.riddles.org.uk> (raw)
In-Reply-To: <CAM37AMPQjpDWNy58W442FSBLEJwLJGgp=xmZts=mj2QzEz-CFw@mail.gmail.com>
References: <CAM37AMPQjpDWNy58W442FSBLEJwLJGgp=xmZts=mj2QzEz-CFw@mail.gmail.com>
>>>>> "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)
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: andrew@tao11.riddles.org.uk, a@abinoda.com, pgsql-performance@lists.postgresql.org
Subject: Re: Why isn't an index scan being used?
In-Reply-To: <87ftsji2tb.fsf@news-spur.riddles.org.uk>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox