up vote 1 down vote favorite

I have a table with 10 million entries, and one of the column is email address. I want to create a hash index on this column since the queries are all on equality comparisons.

Database: Postgres 8.3.3
Machine: 24GB Ram and 16 cores.

Hash index creation takes about 51 minutes, however, if I create a hash index on lower case of the email address, it takes less than 3 minutes!

Example:

  • create index xyz on table_xyz using hash(email): Takes 51 minutes to create this index
  • create index xyz on table_xyz using hash(lower(email)): Takes 3 minutes to create this index.

My maintenance_work_mem is set to 16MB. shared_buffers = 1GB

What explains this behavior?

share|improve this question
0% accept rate
Did this happen once, or did you try it a second time? – willglynn Oct 2 at 18:35
I tried 4 times and got the same result. – phocus Oct 2 at 18:40
3  
I don't know what the answer here is (so, I'm just making a comment). Are you aware that hash indexes are discouraged? From the 8.3 docs... 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. Furthermore, hash index operations are not presently WAL-logged, so hash indexes might need to be rebuilt with REINDEX after a database crash. For these reasons, hash index use is presently discouraged. – David S Oct 2 at 19:06
7  
8.3.3? Time to upgrade, at the very minimum you need to update URGENTLY to 8.3.21, you're missing something like three years of bug fixes including a couple of nasty ones. Then start planning a move to 9.1 or 9.2 before support for 8.3 is dropped completely. – Craig Ringer Oct 2 at 19:07
Check dba instead of stackexchange – Jack Oct 3 at 6:08
feedback

closed as off topic by WATTO Studios, Vikdor, NSPostWhenIdle, Lucifer, Jack Oct 3 at 6:08

Questions on Stack Overflow are expected to relate to programming or software development within the scope defined in the FAQ. Consider editing the question or leaving comments for improvement if you believe the question can be reworded to fit within the scope. Read more about closed questions here.

Browse other questions tagged or ask your own question.