Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 02CAC9FA274 for ; Fri, 27 Oct 2006 10:12:53 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 75372-03-3 for ; Fri, 27 Oct 2006 10:12:44 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from nz-out-0102.google.com (nz-out-0102.google.com [64.233.162.207]) by postgresql.org (Postfix) with ESMTP id 4944C9FB2D8 for ; Fri, 27 Oct 2006 10:12:37 -0300 (ADT) Received: by nz-out-0102.google.com with SMTP id i11so566812nzi for ; Fri, 27 Oct 2006 06:12:36 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=NDf15bab/cHMdrb84z5hQj9IaQZWKaR8XA3VAxc4chya4nsH7u2RnFGcF9Yn7pH/opBOJ3HHKjccit2YTFobaVVOcWOaL4K/k/ks2vxc/gGbRzqxPNVrH5dOtHVXMrJERDkLBWGZjv1FMxhOCIoyqAaOfGR5V8/PRonk8absngE= Received: by 10.35.89.10 with SMTP id r10mr127420pyl; Fri, 27 Oct 2006 06:12:35 -0700 (PDT) Received: by 10.35.31.9 with HTTP; Fri, 27 Oct 2006 06:12:35 -0700 (PDT) Message-ID: <9f2e40a90610270612r75d9f52dh4aaf561995d42bd6@mail.gmail.com> Date: Fri, 27 Oct 2006 18:42:35 +0530 From: "soni de" To: "Dawid Kuroczko" , "A. Kretschmer" Subject: Re: Regarding Bitmap Scan Cc: pgsql-performance@postgresql.org In-Reply-To: <758d5e7f0610170527p8503c1xf51fdfea2742daa1@mail.gmail.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_108522_14990112.1161954755666" References: <9f2e40a90610170439n4b670c23ld586e4f0840e943@mail.gmail.com> <758d5e7f0610170527p8503c1xf51fdfea2742daa1@mail.gmail.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.352 tagged_above=0 required=5 tests=AWL, DNS_FROM_RFC_ABUSE, HTML_50_60, HTML_MESSAGE, SPF_PASS X-Spam-Level: X-Archive-Number: 200610/576 X-Sequence-Number: 21705 ------=_Part_108522_14990112.1161954755666 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline Thanks a lot for your help. Thanks, Soni On 10/17/06, Dawid Kuroczko wrote: > > On 10/17/06, soni de wrote: > > > > I didn't understand the "Bitmap Scan" and the sentence "indexes will be > > dynamically converted to bitmaps in memory". What does mean by "Bitmap Scan" > > in database? > > > > > > > > Can anybody help us regarding above query? > > > > Assume you have a table: > CREATE TABLE foo ( > some_key int, > some_time timestamp with time zone, > some_data text > ); > And two indexes: > CREATE INDEX foo_key ON foo (some_key); > CREATE INDEX foo_time ON foo (some_time); > > Now, you make a query: > SELECT * from foo WHERE some_key > 10 AND some_time > > '2006-10-01'::timestamptz; > > ...originally planner would choose only one index to use -- and would use > the > one which it think its best. > > The 8.1 version does differently: It will scan foo_key index -- make a > bitmap out of it, > scan foo_time index -- make another bitmap out of it, binary AND these > bitmaps, > and will read the data from the table using such combined bitmap. It > could as well > use "OR" if you used OR in your query. > > Hence -- it can be faster, especially for large tables and selective > queries. > > Regards, > DAwid > > > ------=_Part_108522_14990112.1161954755666 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline
Thanks a lot for your help.
 
Thanks,
Soni

 
On 10/17/06, Dawid Kuroczko <qnex42@gmail.com> wrote:
On 10/17/06, soni de < soni.de@gmail.com> wrote:
 

I didn't understand the "Bitmap Scan" and the sentence "indexes will be dynamically converted to bitmaps in memory". What does mean by "Bitmap Scan" in database?

 

Can anybody help us regarding above query?


Assume you have a table:
CREATE TABLE foo (
   some_key int,
   some_time timestamp with time zone,
   some_data text
);
And two indexes:
CREATE INDEX foo_key ON foo (some_key);
CREATE INDEX foo_time ON foo (some_time);

Now, you make a query:
SELECT * from foo WHERE some_key > 10 AND some_time > '2006-10-01'::timestamptz;

...originally planner would choose only one index to use -- and would use the
one which it think its best.

The 8.1 version does differently: It will scan foo_key index -- make a bitmap out of it,
scan foo_time index -- make another bitmap out of it, binary AND these bitmaps,
and will read the data from the table using such combined bitmap.  It could as well
use "OR" if you used OR in your query.

Hence -- it can be faster, especially for large tables and selective queries.

Regards,
   DAwid

 

------=_Part_108522_14990112.1161954755666--