pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feed"Hash index" vs. "b-tree index" (PostgreSQL 8.0)
4+ messages / 4 participants
[nested] [flat]
* "Hash index" vs. "b-tree index" (PostgreSQL 8.0)
@ 2005-05-09 13:40 Ying Lu <ying_lu@cs.concordia.ca>
2005-05-09 14:36 ` Re: "Hash index" vs. "b-tree index" (PostgreSQL 8.0) Neil Conway <neilc@samurai.com>
2005-05-09 22:38 ` Re: "Hash index" vs. "b-tree index" (PostgreSQL 8.0) David Roussel <pgsql-performance@diroussel.xsmail.com>
0 siblings, 2 replies; 4+ messages in thread
From: Ying Lu @ 2005-05-09 13:40 UTC (permalink / raw)
To: pgsql-general@postgresql.org; +Cc: pgsql-performance
Greetings,
We are working on speeding up the queries by creating indexes. We have
queries with searching criteria such as "select ... where *col1='...'*".
This is a simple query with only "=" operation. As a result I setup hash
index on column "col1". While, in postgreSQL 8 doc, it is wirttern:
*Note: * Testing has shown PostgreSQL's hash indexes to perform no
better than B-tree indexes, and the index size and build time for hash
indexes is much worse. For these reasons, hash index use is presently
discouraged.
May I know for simple "=" operation query, for "Hash index" vs. "B-tree"
index, which can provide better performance please?
Thanks,
Emi
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: "Hash index" vs. "b-tree index" (PostgreSQL 8.0)
2005-05-09 13:40 "Hash index" vs. "b-tree index" (PostgreSQL 8.0) Ying Lu <ying_lu@cs.concordia.ca>
@ 2005-05-09 14:36 ` Neil Conway <neilc@samurai.com>
2005-05-09 15:27 ` Re: [PERFORM] "Hash index" vs. "b-tree index" (PostgreSQL 8.0) Christopher Petrilli <petrilli@gmail.com>
1 sibling, 1 reply; 4+ messages in thread
From: Neil Conway @ 2005-05-09 14:36 UTC (permalink / raw)
To: Ying Lu <ying_lu@cs.concordia.ca>; +Cc: pgsql-general@postgresql.org; pgsql-performance
Ying Lu wrote:
> May I know for simple "=" operation query, for "Hash index" vs. "B-tree"
> index, which can provide better performance please?
I don't think we've found a case in which the hash index code
outperforms B+-tree indexes, even for "=". The hash index code also has
a number of additional issues: for example, it isn't WAL safe, it has
relatively poor concurrency, and creating a hash index is significantly
slower than creating a b+-tree index.
-Neil
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: [PERFORM] "Hash index" vs. "b-tree index" (PostgreSQL 8.0)
2005-05-09 13:40 "Hash index" vs. "b-tree index" (PostgreSQL 8.0) Ying Lu <ying_lu@cs.concordia.ca>
2005-05-09 14:36 ` Re: "Hash index" vs. "b-tree index" (PostgreSQL 8.0) Neil Conway <neilc@samurai.com>
@ 2005-05-09 15:27 ` Christopher Petrilli <petrilli@gmail.com>
0 siblings, 0 replies; 4+ messages in thread
From: Christopher Petrilli @ 2005-05-09 15:27 UTC (permalink / raw)
To: Neil Conway <neilc@samurai.com>; +Cc: Ying Lu <ying_lu@cs.concordia.ca>; pgsql-general@postgresql.org; pgsql-performance
On 5/9/05, Neil Conway <neilc@samurai.com> wrote:
> I don't think we've found a case in which the hash index code
> outperforms B+-tree indexes, even for "=". The hash index code also has
> a number of additional issues: for example, it isn't WAL safe, it has
> relatively poor concurrency, and creating a hash index is significantly
> slower than creating a b+-tree index.
This being the case, is there ever ANY reason for someone to use it?
If not, then shouldn't we consider deprecating it and eventually
removing it. This would reduce complexity, I think.
Chris
--
| Christopher Petrilli
| petrilli@gmail.com
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: "Hash index" vs. "b-tree index" (PostgreSQL 8.0)
2005-05-09 13:40 "Hash index" vs. "b-tree index" (PostgreSQL 8.0) Ying Lu <ying_lu@cs.concordia.ca>
@ 2005-05-09 22:38 ` David Roussel <pgsql-performance@diroussel.xsmail.com>
1 sibling, 0 replies; 4+ messages in thread
From: David Roussel @ 2005-05-09 22:38 UTC (permalink / raw)
To: Ying Lu <ying_lu@cs.concordia.ca>; +Cc: pgsql-performance
> *Note: * Testing has shown PostgreSQL's hash indexes to perform no
> better than B-tree indexes, and the index size and build time for hash
> indexes is much worse. For these reasons, hash index use is presently
> discouraged.
>
> May I know for simple "=" operation query, for "Hash index" vs.
> "B-tree" index, which can provide better performance please?
If you trust the documentation use a b-tree. If you don't trust the
documentation do your own tests.
please don't cross post.
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2005-05-09 22:38 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-05-09 13:40 "Hash index" vs. "b-tree index" (PostgreSQL 8.0) Ying Lu <ying_lu@cs.concordia.ca>
2005-05-09 14:36 ` Neil Conway <neilc@samurai.com>
2005-05-09 15:27 ` Christopher Petrilli <petrilli@gmail.com>
2005-05-09 22:38 ` David Roussel <pgsql-performance@diroussel.xsmail.com>
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