
> We could safely sort on the hash value, but I'm not sure how effective
> that would be, considering that we're talking about values that already
> hashed into the same bucket --- there's likely not to be very many
> distinct hash values there.

I think we can safely put that on the todo list.
The existing hash algorithm is very good. So I would on the 
contrary beleive that only a few keys share a hash value per pagesized bucket.
For the equal keys case it does not matter since we want all of the rows anyways.
For the equal hash value case it would probably be best to sort by ctid.

TODO ?: order heap pointers inside hash index pages by hash value and ctid

Andreas

Mon, 7 Jun 2004 10:04:50 +0200
"Zeugswetter Andreas SB SD" <ZeugswetterA@spardat.at>
Re: Why hash indexes suck

Added to TODO:

	* Order heap pointers on hash index pages by hash value and ctid

---------------------------------------------------------------------------

Zeugswetter Andreas SB SD wrote:
> 
> > We could safely sort on the hash value, but I'm not sure how effective
> > that would be, considering that we're talking about values that already
> > hashed into the same bucket --- there's likely not to be very many
> > distinct hash values there.
> 
> I think we can safely put that on the todo list.
> The existing hash algorithm is very good. So I would on the 
> contrary beleive that only a few keys share a hash value per pagesized bucket.
> For the equal keys case it does not matter since we want all of the rows anyways.
> For the equal hash value case it would probably be best to sort by ctid.
> 
> TODO ?: order heap pointers inside hash index pages by hash value and ctid
> 
> Andreas
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend
> 

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Wed, 9 Jun 2004 23:35:04 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Why hash indexes suck
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Added to TODO:
> 	* Order heap pointers on hash index pages by hash value and ctid

[blink]  This seems to miss out on the actual point of the thread (hash
bucket size shouldn't be a disk page) in favor of an entirely
unsupported sub-suggestion.

			regards, tom lane

Wed, 09 Jun 2004 23:59:19 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Why hash indexes suck 
Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > Added to TODO:
> > 	* Order heap pointers on hash index pages by hash value and ctid
> 
> [blink]  This seems to miss out on the actual point of the thread (hash
> bucket size shouldn't be a disk page) in favor of an entirely
> unsupported sub-suggestion.

Yes, I was unsure of the text myself.  I have changed it to:

	* Allow hash buckets to fill disk pages, rather than being
	  sparse

If we sorted the keys, how do we insert new entries efficiently?

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Thu, 10 Jun 2004 00:05:59 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Why hash indexes suck
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Tom Lane wrote:
>> [blink]  This seems to miss out on the actual point of the thread (hash
>> bucket size shouldn't be a disk page) in favor of an entirely
>> unsupported sub-suggestion.

> Yes, I was unsure of the text myself.  I have changed it to:
> 	* Allow hash buckets to fill disk pages, rather than being
> 	  sparse

OK, though maybe "pack hash index buckets onto disk pages more
efficiently" would be clearer.

> If we sorted the keys, how do we insert new entries efficiently?

That is why I called it "unsupported".  I'm not clear what would happen
in buckets that overflow onto multiple pages --- do we try to maintain
ordering across all the pages, or just within a page, or what?  How much
does this buy us versus what it costs to maintain?  Maybe there's a win
there but I think it's pretty speculative ...

			regards, tom lane

Thu, 10 Jun 2004 01:12:34 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Why hash indexes suck 
Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > Tom Lane wrote:
> >> [blink]  This seems to miss out on the actual point of the thread (hash
> >> bucket size shouldn't be a disk page) in favor of an entirely
> >> unsupported sub-suggestion.
> 
> > Yes, I was unsure of the text myself.  I have changed it to:
> > 	* Allow hash buckets to fill disk pages, rather than being
> > 	  sparse
> 
> OK, though maybe "pack hash index buckets onto disk pages more
> efficiently" would be clearer.

OK, updated.


-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Thu, 10 Jun 2004 07:56:47 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Why hash indexes suck