From: Tom Lane <tgl@sss.pgh.pa.us>
To: Neil Conway <neilc@samurai.com>
Cc: Greg Stark <gsstark@mit.edu>
Cc: Jim C. Nasby <decibel@decibel.org>
Cc: Christopher Petrilli <petrilli@gmail.com>
Cc: Ying Lu <ying_lu@cs.concordia.ca>
Cc: pgsql-general@postgresql.org
Cc: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] "Hash index" vs. "b-tree index" (PostgreSQL
Date: Wed, 11 May 2005 09:57:07 -0400
Message-ID: <3485.1115819827@sss.pgh.pa.us> (raw)
In-Reply-To: <428209BA.8070907@samurai.com>
References: <427F6853.90308@cs.concordia.ca>
<427F757A.8000407@samurai.com>
<59d991c405050908271b55e673@mail.gmail.com>
<427F8321.5030702@samurai.com>
<20050509163423.GH35026@decibel.org>
<427F9211.2090008@samurai.com>
<20050509165133.GJ35026@decibel.org>
<427FFCD3.1010803@samurai.com>
<4407.1115698257@sss.pgh.pa.us>
<428037A2.4060304@samurai.com>
<6686.1115700888@sss.pgh.pa.us>
<878y2nsj7i.fsf@stark.xeocode.com>
<428209BA.8070907@samurai.com>
Neil Conway <neilc@samurai.com> writes:
> Greg Stark wrote:
>> What if the hash index stored *only* the hash code?
> Attached is a WIP patch that implements this.
Performance?
> I'm posting mainly because I wasn't sure what to do to avoid false
> positives in the case of hash collisions. In the hash AM code it is
> somewhat awkward to fetch the pointed-to heap tuple and recheck the
> scankey.[1] I just did the first thing that came to mind -- I marked all
> the hash AM opclasses as "lossy", so the index qual is rechecked. This
> works, but suggestions for a better way to do things would be welcome.
AFAICS that's the *only* way to do it.
I disagree completely with the idea of forcing this behavior for all
datatypes. It could only be sensible for fairly wide values; you don't
save enough to justify the lossiness otherwise.
It would be interesting to look into whether it could be driven on a
per-opclass basis. Then you could have, eg, "text_lossy_hash_ops"
as a non-default opclass the DBA could select if he wanted this
behavior. (The code could perhaps use the amopreqcheck flag to tell
it which way to behave.) If that seems unworkable, I'd prefer to see us
set this up as a new index AM type, which would share a lot of code with
the old.
[ BTW, posting patches to pgsql-general seems pretty off-topic. ]
regards, tom lane
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-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, neilc@samurai.com, gsstark@mit.edu, decibel@decibel.org, petrilli@gmail.com, ying_lu@cs.concordia.ca, pgsql-general@postgresql.org
Subject: Re: [PERFORM] "Hash index" vs. "b-tree index" (PostgreSQL
In-Reply-To: <3485.1115819827@sss.pgh.pa.us>
* 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