Lists: | pgsql-performance |
---|
From: | "soni de" <soni(dot)de(at)gmail(dot)com> |
---|---|
To: | pgsql-performance(at)postgresql(dot)org |
Subject: | Regarding Bitmap Scan |
Date: | 2006-10-17 11:39:29 |
Message-ID: | 9f2e40a90610170439n4b670c23ld586e4f0840e943@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-performance |
Hello,
I was going through the Performance Enhancements of 8.1.0, in that I have
read "Bitmap Scan"
"*Bitmap Scan:* indexes will be dynamically converted to bitmaps in memory
when appropriate, giving up to twenty times faster index performance on
complex queries against very large tables. This also helps simplify database
management by greatly reducing the need for multi-column indexes."
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?
Thanks,
Soni
From: | "A(dot) Kretschmer" <andreas(dot)kretschmer(at)schollglas(dot)com> |
---|---|
To: | pgsql-performance(at)postgresql(dot)org |
Subject: | Re: Regarding Bitmap Scan |
Date: | 2006-10-17 11:45:11 |
Message-ID: | 20061017114511.GD19268@a-kretschmer.de |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-performance |
am Tue, dem 17.10.2006, um 17:09:29 +0530 mailte soni de folgendes:
> 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?
For instance, you have a large table with 5 indexes on this and a query
that checks conditions on this 5 columns.
PG is now able to combine this 5 indexes and performs only 1 bitmap
index scan on this table, and not 5 independet nested bitmap scans.
A realy very great performance-boost!
HTH, Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47215, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
From: | "Dawid Kuroczko" <qnex42(at)gmail(dot)com> |
---|---|
To: | "soni de" <soni(dot)de(at)gmail(dot)com> |
Cc: | pgsql-performance(at)postgresql(dot)org |
Subject: | Re: Regarding Bitmap Scan |
Date: | 2006-10-17 12:27:31 |
Message-ID: | 758d5e7f0610170527p8503c1xf51fdfea2742daa1@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-performance |
On 10/17/06, soni de <soni(dot)de(at)gmail(dot)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
From: | "soni de" <soni(dot)de(at)gmail(dot)com> |
---|---|
To: | "Dawid Kuroczko" <qnex42(at)gmail(dot)com>, "A(dot) Kretschmer" <andreas(dot)kretschmer(at)schollglas(dot)com> |
Cc: | pgsql-performance(at)postgresql(dot)org |
Subject: | Re: Regarding Bitmap Scan |
Date: | 2006-10-27 13:12:35 |
Message-ID: | 9f2e40a90610270612r75d9f52dh4aaf561995d42bd6@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-performance |
Thanks a lot for your help.
Thanks,
Soni
On 10/17/06, Dawid Kuroczko <qnex42(at)gmail(dot)com> wrote:
>
> On 10/17/06, soni de <soni(dot)de(at)gmail(dot)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
>
>
>