From: Pierre Frédéric Caillaud <lists@peufeu.com>
To: Matthew Wakeling <matthew@flymine.org>
To: Mathieu De Zutter <mathieu@dezutter.org>
Cc: Robert Haas <robertmhaas@gmail.com>
Cc: Kevin Grittner <Kevin.Grittner@wicourts.gov>
Cc: pgsql-performance@postgresql.org
Subject: Re: Choice of bitmap scan over index scan
Date: Mon, 11 Jan 2010 12:23:53 +0100
Message-ID: <op.u6c8l3xucke6l8@soyouz> (raw)
In-Reply-To: <alpine.DEB.2.00.1001111029040.25000@aragorn.flymine.org>
References: <4B49A3AF020000250002E138@gw.wicourts.gov>
<603c8f071001101852n7ce69d9atfc0b7aa65d8e357d@mail.gmail.com>
<d4468d971001110036s22796d2bud01f8ec38fd21ce0@mail.gmail.com>
<alpine.DEB.2.00.1001111029040.25000@aragorn.flymine.org>
> Postgres is being conservative. The plan it uses (bitmap index scan)
> will perform much better than an index scan when the data is not in the
> cache, by maybe an order of magnitude, depending on your hardware setup.
>
> The index scan may perform better at the moment, but the bitmap index
> scan is safer.
Suppose you make a query that will need to retrieve 5% of the rows in a
table...
If the table is nicely clustered (ie you want the latest rows in a table
where they are always appended at the end with no holes, for instance),
bitmap index scan will mark 5% of the pages for reading, and read them
sequentially (fast). Plain index scan will also scan the rows more or less
sequentially, so it's going to be quite fast too.
Now if your table is not clustered at all, or clustered on something
which has no correlation to your current query, you may hit the worst case
: reading a ramdom sampling of 5% of the pages. Bitmap index scan will
sort these prior to reading, so the HDD/OS will do smart things. Plain
index scan won't.
- worst case for bitmap index scan is a seq scan... slow, but if you have
no other choice, it's OK.
- worst case for plain index scan is a lot worse since it's a random
seekfest.
If everything is cached in RAM, there is not much difference (plain index
scan can be faster if the bitmap "recheck cond" is slow).
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: lists@peufeu.com, matthew@flymine.org, mathieu@dezutter.org, robertmhaas@gmail.com, Kevin.Grittner@wicourts.gov
Subject: Re: Choice of bitmap scan over index scan
In-Reply-To: <op.u6c8l3xucke6l8@soyouz>
* 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