Indexes

• Indexes vs Indices
   
• Purpose
   
• Types: B-tree, R-tree, hash, and GiST.

       Testing has shown PostgreSQL's hash indexes to be similar or slower
       than B-tree indexes, and the index size and build time for hash
       indexes is much worse. Hash indexes also suffer poor performance
       under high concurrency. For these reasons, hash index use is
       discouraged.
  

• Functional indexes
  CREATE INDEX lower_artist ON venue (LOWER(aname))
   
• Partial indexes
  CREATE INDEX good_artist ON venue WHERE type != 'rap'
   
• Nulls as a partial
  CREATE INDEX no_artist ON venue WHERE aname IS NULL
   
• The importance of ANALYZE and pg_statistics
   
• Using a range to force an index
  SELECT * FROM venue WHERE instruments > 3 AND instruments < 10
   
• Hidden costs: MVCC, index bloat (fixed in 7.4 using fsm mojo)

Previous: Functions         [Table of Contents]         Next: IN and EXISTS