Index
Indexes are data which has information where the data is located, this takes up more space on the database server, Indexes are built on B Trees , all the data is available in such a way that the look up time is reduced, this is especially useful when you need exact matches or prefix based search, the indexes use hashes to for exact matches in the database during the search and completely unusable for prefix matching, The database decides what function or type of INDEX has to be used with the column, such as for a PRIMARY KEY Database Constraints the index uses hashing by default and for a UNIQUE constraint it would use a B-Tree, at the end of the day all of this is automatically managed by the Database software
When using indexes with TEXT data there is a very high possibility that the entire column can be duplicated in the INDEX as well, this results in a INDEX size sometimes on par or greater than the data itself.
Using Explain Analyze statements we can see the query performance with and without an INDEX and the type of the Index as well.
B-Tree indexes are good for sorting, exact match lookup and prefix matching, these are not good for non-prefix matching, they don’t bring any value or performance to the table.
Hash Indexes are good for exact matches
When using a index it is also important to know what indexing strategy to use. for example using an index speeds things up, but we can also fine tune the this process by creating a separate column just for the Hash value and use this column for the search which further speeds things up!
Here are some of the types of Indexes we have in PostgreSQL
- B Trees Index - The default Index for most applications
- Hash Index - Quick Lookup of long Key Strings
- Block Range Index - BRIN - Smaller / Faster if data is mostly sorted
- Generalized Lookup Index - GIN - multiple values in a column
- Generalized Search Tree - GiST
- Space Partitoned Search Tree- GiST
Read more about PostgreSQL Indexes
Compound (Multi-Column) Indexes
When a query filters on multiple columns, create one compound index on all the columns together — not separate indexes on each column:
-- Good: one compound index for queries filtering on both
CREATE INDEX idx_orders_cust_date ON orders (customer_id, order_date);
-- Less effective: separate single-column indexes
CREATE INDEX idx_cust ON orders (customer_id);
CREATE INDEX idx_date ON orders (order_date);Column order in a compound index matters — the index can be used for queries filtering on a prefix of the columns (leftmost first). (customer_id, order_date) helps queries on customer_id alone or customer_id + order_date, but NOT order_date alone.
Auto-Indexing Behavior
| DBMS | Primary Key auto-indexed? | Foreign Key auto-indexed? |
|---|---|---|
| PostgreSQL | Yes | No — must create manually |
| MySQL (InnoDB) | Yes (clustered index) | Yes |
| SQLite | No (uses implicit rowid) | No |
| Oracle | Yes | No |
Always check your DBMS. A missing Foreign Key index is one of the most common performance problems — joins and CASCADE deletes become full table scans.
When to Use Indexes
| Scenario | Index? | Why |
|---|---|---|
| Column in WHERE with high selectivity | Yes | Avoids full table scan |
| JOIN column (Foreign Key) | Yes | Speeds up join lookups |
| ORDER BY column | Yes | Avoids sort operation |
| Low-cardinality column (boolean, status) | Usually no | Full scan may be cheaper |
| Small table (< 1000 rows) | No | Sequential scan is fast enough |
| Bulk loading data | Drop first | Rebuild after load is faster |
Use Explain Analyze to verify whether the optimizer is actually using your index.
Inverted Index
Inverted Indexes are used primarily for searching and building search engines, it is usually used with TEXT. The GIN index in PostgreSQL implements inverted indexing.
Inverted Index takes a bunch of documents or data and splits them into keywords and ranks them in order to find them. See Full Text Search for how to use this with tsvector and tsquery in PostgreSQL.