Index in PostgreSql

Index in PostgreSql

Postgres supports many different index types:

B-Tree is the default that you get when you do CREATE INDEX. Virtually all databases will have some B-tree indexes. The B stands for Balanced, and the idea is that the amount of data on both sides of the tree is roughly the same. Therefore the number of levels that must be traversed to find rows is always in the same ballpark. B-Tree indexes can be used for equality and range queries efficiently. They can operate against all datatypes, and can also be used to retrieve NULL values. Btrees are designed to work very well with caching, even when only partially cached.

Hash Indexes are only useful for equality comparisons, but you pretty much never want to use them since they are not transaction safe, need to be manually rebuilt after crashes, and are not replicated to followers, so the advantage over using a B-Tree is rather small.

Generalized Inverted Indexes (GIN) are useful when an index must map many values to one row, whereas B-Tree indexes are optimized for when a row has a single key value. GINs are good for indexing array values as well as for implementing full-text search.

Generalized Search Tree (GiST) indexes allow you to build general balanced tree structures, and can be used for operations beyond equality and range comparisons. They are used to index the geometric data types, as well as full-text search.

Partial Index:
an index with a where clause.

Expression Indexes:

These indexes are useful for queries that match on some function or modification of your data. The classic example for an expression-based index is when you typically search based on a name only after converting it to a particular case.
CREATE INDEX idx_system_emails_to_email_address ON t (lower(to_email_address));
SELECT * FROM t WHERE lower(to_email_address) = ‘x’;

Unique Indexes:

  • Unique index guarantees that the table won’t have more than one row with the same value.
  • It’s advantageous to create unique indexes for two reasons: data integrity and performance.
  • Look-ups on a unique index are generally very fast.

Multi-column Indexes:

Sorted Indexes:

Share this post

Leave a comment

Your email address will not be published.