Message-ID: <44CA8BC4.2080204@dunslane.net>
Date: Fri, 28 Jul 2006 18:12:20 -0400
From: Andrew Dunstan <andrew@dunslane.net>
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US;
	rv:1.8.0.4) Gecko/20060607 Fedora/1.0.2-1.fc5 SeaMonkey/1.0.2
MIME-Version: 1.0
To: Tom Lane <tgl@sss.pgh.pa.us>
CC: Dann Corbit <DCorbit@connx.com>, Luke Lonergan <llonergan@greenplum.com>,
	"Jim C. Nasby" <jnasby@pervasive.com>, Jie Zhang <jzhang@greenplum.com>,
	Mark Kirkwood <markir@paradise.net.nz>,
	Josh Berkus <josh@agliodbs.com>, Gavin Sherry <swm@linuxworld.com.au>,
	pgsql-hackers@postgresql.org
Subject: Re: On-disk bitmap index patch
References: 
 <D425483C2C5C9F49B5B7A41F8944154757DAC7@postal.corporate.connx.com>
	<809.1154117933@sss.pgh.pa.us>
In-Reply-To: <809.1154117933@sss.pgh.pa.us>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Tom Lane wrote:
>
> Both of these pages say up front that they are considering read-only
> data.  So one of the questions that has to be answered (and the
> submitters have been entirely mum about) is exactly how bad is the
> update performance?  If it's really awful that's going to constrain
> the use cases quite a lot, whereas merely "a bit slower than btree"
> wouldn't be such a problem.
>
> In any case, arguing that other DBs find it's a win will cut no ice
> with me.  See adjacent discussion about hash indexes --- those *ought*
> to be a win, but they aren't in Postgres, for reasons that are still
> guesses.  The translation gap between other DBs' experience and ours
> can be large.
>   


Notwithstanding that, I have a couple of non-postgres data points / 
anecdotes on this.

Back in my days as an Ingres DBA in the mid 90s, our fairly highly tuned 
system used hash organised tables only for small fairly static 
lookup-type tables (state codes, postcodes, etc). Everything that was 
more dynamic was done with btree indexed tables.

A few years later, I was producing very large tables of email addresses 
using BDB. I quickly stopped using hash tables when I found that the 
reorganisation penalty was huge. Switching to btree worked just fine, 
with no sudden performance blip. This might not be directly relevant, 
but clearly the bucket size is.

I guess what we need to demonstrate is that the better hash performance 
will actually persist to a scale where it is actually worth it - surely 
for very small tables the index method won't matter much anyway.

cheers

andrew