Received: from localhost (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id 0BF96633418 for ; Mon, 11 Jan 2010 07:23:27 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024) with ESMTP id 75502-03 for ; Mon, 11 Jan 2010 11:23:10 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from 63.mail-out.ovh.net (63.mail-out.ovh.net [91.121.185.56]) by mail.postgresql.org (Postfix) with SMTP id 105E46333DE for ; Mon, 11 Jan 2010 07:23:15 -0400 (AST) Received: (qmail 16741 invoked by uid 503); 11 Jan 2010 11:23:49 -0000 Received: from b6.ovh.net (HELO mail609.ha.ovh.net) (213.186.33.56) by 63.mail-out.ovh.net with SMTP; 11 Jan 2010 11:23:49 -0000 Received: from b0.ovh.net (HELO queueout) (213.186.33.50) by b0.ovh.net with SMTP; 11 Jan 2010 11:23:12 -0000 Received: from par69-8-88-161-102-87.fbx.proxad.net (HELO soyouz) (88.161.102.87) by ns0.ovh.net with SMTP; 11 Jan 2010 11:23:12 -0000 Date: Mon, 11 Jan 2010 12:23:53 +0100 To: "Matthew Wakeling" , "Mathieu De Zutter" Subject: Re: Choice of bitmap scan over index scan From: =?utf-8?Q?Pierre_Fr=C3=A9d=C3=A9ric_Caillau?= =?utf-8?Q?d?= Cc: "Robert Haas" , "Kevin Grittner" , pgsql-performance@postgresql.org Content-Type: text/plain; format=flowed; delsp=yes; charset=utf-8 MIME-Version: 1.0 References: <4B49A3AF020000250002E138@gw.wicourts.gov> <603c8f071001101852n7ce69d9atfc0b7aa65d8e357d@mail.gmail.com> Content-Transfer-Encoding: 7bit Message-ID: In-Reply-To: User-Agent: Opera Mail/9.62 (Linux) X-Ovh-Tracer-Id: 729301664849200837 X-Ovh-Remote: 88.161.102.87 (par69-8-88-161-102-87.fbx.proxad.net) X-Ovh-Local: 213.186.33.20 (ns0.ovh.net) X-Spam-Check: DONE|U 0.5/N X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-2.494 tagged_above=-10 required=5 tests=AWL=0.105, BAYES_00=-2.599 X-Spam-Level: X-Archive-Number: 201001/185 X-Sequence-Number: 37065 > 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).