pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Hash index
10+ messages / 5 participants
[nested] [flat]

* Hash index
@ 2005-08-26 10:59  RAJU  kumar <raju_19db@rediffmail.com>
  0 siblings, 2 replies; 10+ messages in thread

From: RAJU kumar @ 2005-08-26 10:59 UTC (permalink / raw)
  To: pgsql-admin

  
  
i want to find out the difference between the btree index and hash index and how exactly the hash index work.


^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: Hash index
@ 2005-08-27 06:21  Bruno Wolff III <bruno@wolff.to>
  parent: RAJU  kumar <raju_19db@rediffmail.com>
  1 sibling, 1 reply; 10+ messages in thread

From: Bruno Wolff III @ 2005-08-27 06:21 UTC (permalink / raw)
  To: RAJU kumar <raju_19db@rediffmail.com>; +Cc: pgsql-admin

On Fri, Aug 26, 2005 at 10:59:50 -0000,
  RAJU  kumar <raju_19db@rediffmail.com> wrote:
>   
>   
> i want to find out the difference between the btree index and hash index and how exactly the hash index work.

Why? You probably don't want to use hash indexes as btrees are virtually
always better in the current implementation.



^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: Hash index
@ 2005-08-27 13:51  Bruno Wolff III <bruno@wolff.to>
  0 siblings, 0 replies; 10+ messages in thread

From: Bruno Wolff III @ 2005-08-27 13:51 UTC (permalink / raw)
  To: RAJU kumar <raju_19db@rediffmail.com>; +Cc: pgsql-admin

Please keep replies posted to the list so that more people can add to and
learn from the discussion.

On Sat, Aug 27, 2005 at 08:18:57 -0000,
  RAJU  kumar <raju_19db@rediffmail.com> wrote:
> Sir i am a consultant in Sql * international india,
> And the problem with me is i have to handle all the question regarding oracle i now how btree works and also about bitmap but i want to know abt hash index and how and where we should use it.
> Hope u will help me .
> raju

The short answer is that you don't want to use hash indexes in postgres.



^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: Hash index
@ 2005-08-27 14:35  Jim C. Nasby <jnasby@pervasive.com>
  parent: Bruno Wolff III <bruno@wolff.to>
  0 siblings, 0 replies; 10+ messages in thread

From: Jim C. Nasby @ 2005-08-27 14:35 UTC (permalink / raw)
  To: Bruno Wolff III <bruno@wolff.to>; RAJU kumar <raju_19db@rediffmail.com>; pgsql-admin

On Sat, Aug 27, 2005 at 01:21:50AM -0500, Bruno Wolff III wrote:
> On Fri, Aug 26, 2005 at 10:59:50 -0000,
>   RAJU  kumar <raju_19db@rediffmail.com> wrote:
> >  ?
> >  ?
> > i want to find out the difference between the btree index and hash index and how exactly the hash index work.
> 
> Why? You probably don't want to use hash indexes as btrees are virtually
> always better in the current implementation.

That may be true but it's still good to be able to see what they
actually do and how they work. In particular it would be good to know
what relation they have to the hash operations (HashAgg, HashJoin, etc).
-- 
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software        http://pervasive.com        512-569-9461



^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: Hash index
@ 2005-08-30 18:53  Scott Marlowe <smarlowe@g2switchworks.com>
  parent: RAJU  kumar <raju_19db@rediffmail.com>
  1 sibling, 1 reply; 10+ messages in thread

From: Scott Marlowe @ 2005-08-30 18:53 UTC (permalink / raw)
  To: RAJU kumar <raju_19db@rediffmail.com>; +Cc: pgsql-admin

On Fri, 2005-08-26 at 05:59, RAJU kumar wrote:
>   
>   
> i want to find out the difference between the btree index and hash
> index and how exactly the hash index work.

How do hash indexes work in postgresql?  Poorly.  badumpbump.  Thanks,
I'll be here all week (bad western humor, sorry.)

Seriously, there are issues that pop up here every so often that stump
people until we find out that they're using hash indexes and every goes
"Ohhhhh!  That's why X isn't working for you."

Currently, only btree indexes are handled properly in case of a server
crash (other indexes may get corrupted silently) and they are generally
as fast as or faster than hashes.

The basic concept in postgresql is the same as elsewhere, but they've
never been fleshed out properly, especially compared to btree, which
have had lots of testing and fixing and performance tuning and such.

Personally, I think that when one creates a non-btree index, one should
get a warning saying that non-btree indexes are not necessarily
transactionally safe in the event of a crash.



^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: Hash index
@ 2005-08-30 19:32  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Scott Marlowe <smarlowe@g2switchworks.com>
  0 siblings, 2 replies; 10+ messages in thread

From: Tom Lane @ 2005-08-30 19:32 UTC (permalink / raw)
  To: Scott Marlowe <smarlowe@g2switchworks.com>; +Cc: RAJU kumar <raju_19db@rediffmail.com>; pgsql-admin

Scott Marlowe <smarlowe@g2switchworks.com> writes:
> Personally, I think that when one creates a non-btree index, one should
> get a warning saying that non-btree indexes are not necessarily
> transactionally safe in the event of a crash.

As of 8.1, GIST indexes are WAL-logged, so that would be inappropriate
anyway.

What I foresee happening (soon, 8.2 maybe) is that rtree will go away
completely; there isn't anything it can do that GIST can't do as well or
better, so there's no point in expending maintenance resources on it.
We're really only leaving it there for 8.1 because there's so much new,
as-yet-poorly-tested code involved in the GIST replacement opclasses.

That leaves hash.  I'm hoping someone will step up and do WAL logging
for hash in the near future.  Unlike rtree, I'm not expecting that we
might get rid of hash indexes.  Even if the performance problems never
get fixed, we use hash index opclasses to manage datatype-specific
hashing for hash joins, hash aggregation, etc, so if we removed hash
indexes we'd need to find some other representation for all that.

Anyway, that's my two cents on the development roadmap for indexes.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: Hash index
@ 2005-08-30 20:17  Jim C. Nasby <jnasby@pervasive.com>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  1 sibling, 1 reply; 10+ messages in thread

From: Jim C. Nasby @ 2005-08-30 20:17 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Scott Marlowe <smarlowe@g2switchworks.com>; RAJU kumar <raju_19db@rediffmail.com>; pgsql-admin

On Tue, Aug 30, 2005 at 03:32:26PM -0400, Tom Lane wrote:
> That leaves hash.  I'm hoping someone will step up and do WAL logging
> for hash in the near future.  Unlike rtree, I'm not expecting that we
> might get rid of hash indexes.  Even if the performance problems never
> get fixed, we use hash index opclasses to manage datatype-specific
> hashing for hash joins, hash aggregation, etc, so if we removed hash
> indexes we'd need to find some other representation for all that.

So does that mean a hash index could (theoretically) improve the
performance of a hash join or hash aggregation?
-- 
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software        http://pervasive.com        512-569-9461



^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: Hash index
@ 2005-08-30 20:20  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Jim C. Nasby <jnasby@pervasive.com>
  0 siblings, 1 reply; 10+ messages in thread

From: Tom Lane @ 2005-08-30 20:20 UTC (permalink / raw)
  To: Jim C. Nasby <jnasby@pervasive.com>; +Cc: Scott Marlowe <smarlowe@g2switchworks.com>; RAJU kumar <raju_19db@rediffmail.com>; pgsql-admin

"Jim C. Nasby" <jnasby@pervasive.com> writes:
> So does that mean a hash index could (theoretically) improve the
> performance of a hash join or hash aggregation?

I don't think so --- the whole point of a hash is to do your matching
in-memory, rather than jumping all over the disk for it ...

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: Hash index
@ 2005-08-30 20:54  Scott Marlowe <smarlowe@g2switchworks.com>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  1 sibling, 0 replies; 10+ messages in thread

From: Scott Marlowe @ 2005-08-30 20:54 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: RAJU kumar <raju_19db@rediffmail.com>; pgsql-admin

On Tue, 2005-08-30 at 14:32, Tom Lane wrote:
> Scott Marlowe <smarlowe@g2switchworks.com> writes:
> > Personally, I think that when one creates a non-btree index, one should
> > get a warning saying that non-btree indexes are not necessarily
> > transactionally safe in the event of a crash.
> 
> As of 8.1, GIST indexes are WAL-logged, so that would be inappropriate
> anyway.

Only in this exact instance.  It still might make sense to emit such a
warning / notice when using a non Wal logged index of any kind though.

Glad that GiST indexes got the WAL logging, btw, my tanks to whoever did
the work.



^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: Hash index
@ 2005-08-30 23:39  Jim C. Nasby <jnasby@pervasive.com>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 10+ messages in thread

From: Jim C. Nasby @ 2005-08-30 23:39 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Scott Marlowe <smarlowe@g2switchworks.com>; RAJU kumar <raju_19db@rediffmail.com>; pgsql-admin

On Tue, Aug 30, 2005 at 04:20:03PM -0400, Tom Lane wrote:
> "Jim C. Nasby" <jnasby@pervasive.com> writes:
> > So does that mean a hash index could (theoretically) improve the
> > performance of a hash join or hash aggregation?
> 
> I don't think so --- the whole point of a hash is to do your matching
> in-memory, rather than jumping all over the disk for it ...

True, but if you have an index on the value you're hashing you should be
able to find out very quickly if it exists or not, no? So if you were
doing a hash join between two tables and one of them had a hash index on
the appropriate field, couldn't you check the index first to see if
there was a match? Granted, you could probably do the same thing with a
B-tree index, but this might be faster in some scenarios.
-- 
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software        http://pervasive.com        512-569-9461



^ permalink  raw  reply  [nested|flat] 10+ messages in thread


end of thread, other threads:[~2005-08-30 23:39 UTC | newest]

Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-08-26 10:59 Hash index RAJU  kumar <raju_19db@rediffmail.com>
2005-08-27 06:21 ` Bruno Wolff III <bruno@wolff.to>
2005-08-27 14:35   ` Jim C. Nasby <jnasby@pervasive.com>
2005-08-30 18:53 ` Scott Marlowe <smarlowe@g2switchworks.com>
2005-08-30 19:32   ` Tom Lane <tgl@sss.pgh.pa.us>
2005-08-30 20:17     ` Jim C. Nasby <jnasby@pervasive.com>
2005-08-30 20:20       ` Tom Lane <tgl@sss.pgh.pa.us>
2005-08-30 23:39         ` Jim C. Nasby <jnasby@pervasive.com>
2005-08-30 20:54     ` Scott Marlowe <smarlowe@g2switchworks.com>
2005-08-27 13:51 Re: Hash index Bruno Wolff III <bruno@wolff.to>

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