PostgreSQL

Indexes

• Types
  • b-tree default, for inequality, like 'xx%', order by
  • hash useful for equality predicate
  • gist are a family of indexes that are suitable for nearest neighbor kind searches, e.g. sp-gist
  • GIN are inverted indexes store values only once with compressed list of matching row locations
  • BRIN or Block Range INdex store summaries about the values stored in consecutive physical blocks
• partial indexes are sparse indexes that index only partial data
• expression indexes are index on column expressions
• global indexes cover more than one table, mainly for partitioned child tables