From: Mark Kirkwood <markir@paradise.net.nz>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Josh Berkus <josh@agliodbs.com>
Cc: Gavin Sherry <swm@linuxworld.com.au>
Cc: Jie Zhang <jzhang@greenplum.com>
Cc: pgsql-hackers@postgresql.org, Luke Lonergan <LLonergan@greenplum.com>
Subject: Re: On-disk bitmap index patch
Date: Thu, 27 Jul 2006 14:00:18 +1200
Message-ID: <44C81E32.8090500@paradise.net.nz> (raw)
In-Reply-To: <1593.1153923961@sss.pgh.pa.us>
References: <C0E1CD9D.8DC0%jzhang@greenplum.com>
<Pine.LNX.4.58.0607240720180.19004@linuxworld.com.au>
<28525.1153698215@sss.pgh.pa.us>
<Pine.LNX.4.58.0607240952250.19854@linuxworld.com.au>
<28750.1153700718@sss.pgh.pa.us>
<44C6652C.1020203@agliodbs.com>
<24309.1153860389@sss.pgh.pa.us>
<44C6C8CB.9030802@paradise.net.nz>
<1593.1153923961@sss.pgh.pa.us>
Tom Lane wrote:
>
>
> I'm surprised no one caught me making this bogus computation. I
> realized this morning it's wrong: if there are 10000 distinct values
> then on the average the 1-bits would be about 10000 bits apart, not 100.
Right - I didn't think 10000 was *that* bad, but was too sleepy to try
working it out :-).
>
>
> I don't believe the 100x numbers that have been
> bandied around in this discussion, but 10x is plenty enough to be
> interesting.
>
Yep - I have not managed to get 100x in any of my tests. However, I do
see some about half that for the TPCH scale 10 dataset:
tpch=# \i relsizes.sql (BTREE)
relname | relpages
------------------------+----------
customer | 41019
customer_c_custkey | 3288
customer_c_mktsegment | 5779
customer_c_nationkey | 3288
lineitem | 1535724
lineitem_l_linenumber | 131347
lineitem_l_orderkey | 131347
orders | 307567
orders_o_custkey | 32847
orders_o_orderpriority | 65876
orders_o_orderstatus | 41131
tpch=# \i relsizes.sql (MAINLY BITMAP)
relname | relpages
------------------------+----------
customer | 41019
customer_c_custkey | 3288
customer_c_mktsegment | 157
customer_c_nationkey | 336
lineitem | 1535724
lineitem_l_linenumber | 7571
lineitem_l_orderkey | 131347
orders | 307567
orders_o_custkey | 32847
orders_o_orderpriority | 1427
orders_o_orderstatus | 717
The orders_o_orderpriority and orders_o_orderstatus bitmap indexes are
46 and 57 times smaller than their btree counterparts (hmm...might we
see even better compression for larger scale factors?).
An obvious deduction is that the TPCH dataset is much more amenable to
run compression than my synthetic Zipfian data was. The interesting
question is how well "real" datasets are run compressable, I suspect
"better than my Zipfian data" is a safe assumption!
Cheers
Mark
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, tgl@sss.pgh.pa.us, josh@agliodbs.com, swm@linuxworld.com.au, jzhang@greenplum.com, LLonergan@greenplum.com
Subject: Re: On-disk bitmap index patch
In-Reply-To: <44C81E32.8090500@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