pg.ddx.io  pgsql-hackers@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: Mark Kirkwood <markir@paradise.net.nz>
To: Jie Zhang <jzhang@greenplum.com>
Cc: Hannu Krosing <hannu@skype.net>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Gavin Sherry <swm@linuxworld.com.au>
Cc: pgsql-hackers@postgresql.org, Luke Lonergan <LLonergan@greenplum.com>
Subject: Re: On-disk bitmap index patch
Date: Tue, 25 Jul 2006 15:54:48 +1200
Message-ID: <44C59608.6030207@paradise.net.nz> (raw)
In-Reply-To: <C0EAB84F.8FB4%jzhang@greenplum.com>
References: <C0EAB84F.8FB4%jzhang@greenplum.com>

Jie Zhang wrote:
> 
> On 7/24/06 6:59 AM, "Hannu Krosing" <hannu@skype.net> wrote:
> 
>>
>>
>> And also for AND-s of several indexes, where indexes are BIG, your btree
>> indexes may be almost as big as tables but the resulting set of pages is
>> small.
> 
> Yeah, Hannu points it out very well -- the bitmap index works very well when
> columns have low cardinalities and AND operations will produce small number
> of results.
> 
> Also, the bitmap index is very small in low cardinality cases, where the
> btree tends to take up at least 10 times more space.
> 
> 

The smallness of the bitmap index also means that some queries will 
require much less work_mem to achieve good performance e.g consider:

  TPCH dataset with scale factor 10 on my usual PIII HW, query -
  select count(*) from lineitem where l_linenumber=1;

This executes in about 100 seconds with work_mem = 20M if there is a 
bitmap index on l_linenumber. It takes 3832 seconds (!) if there is a 
btree index on the same column. Obviously cranking up work_mem will even 
up the difference (200M gets the btree to about 110 seconds), but being 
able to get good performance with less memory is a good thing!

Cheers

Mark



view thread (52+ messages)  latest in thread

Message-ID: <44C59608.6030207@paradise.net.nz>
Permalink:  ../44C59608.6030207@paradise.net.nz/
Also on:    postgresql.org/message-id/44C59608.6030207@paradise.net.nz

 · 

reply
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-hackers@postgresql.org
  Cc: markir@paradise.net.nz, jzhang@greenplum.com, hannu@skype.net, tgl@sss.pgh.pa.us, swm@linuxworld.com.au, LLonergan@greenplum.com
  Subject: Re: On-disk bitmap index patch
  In-Reply-To: <44C59608.6030207@paradise.net.nz>

* 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