2

Self-explanatory question. I have tested on a very large PostgreSQL table (+500M rows) where a B-TREE index takes around ~30 minutes to be created on a column which contains UUID or email-sized strings, while a hash index addition operation hasn't finished in hours.

What difference in their mechanism causes this?

(Referring to Postgres versions 12 and 13)

Z. M.
  • 329
  • 5
  • 13
  • Maybe the second statement is waiting to get a lock? –  Jul 07 '21 at 14:42
  • There are many possibilities. Use the system tools (`top`, `sar`, `vmstat`) to identify the bottleneck for the hash index build. Is it CPU or disk waits? How large are the indexes? (you will have to use file system tools for the hash index, as you can't query the size of an index which is still in process.) – jjanes Jul 07 '21 at 15:30

0 Answers0