What is Index

Something you create for a column in a table to speed up search queries involving that column.

Clustered Index

Binary tree.

Records stored in order according to the clustered index.

Arrangement of actual records on secondary storage.

Data modification is slowed since the tree needs to be re-balanced.

Only one clustered index is allowed per table.

Does not need additional storage.

Composite Index

Multiple columns used for indexing.

Unclusterd Index

Heap data structure.

Records are in order with a pointer to a record.

Copy of entire unclustered index is stored in memory.

Binary serach is performed on the copied index.

Can have multiple unclustered indexes per table.

Requires additional storage.

Primary Keys are always indexed

In most SQL databases, Primary keys are automatically indexed.

Finding Indexes

SELECT
    tablename,
    indexname,
    indexdef
FROM
    pg_indexes
WHERE
    schemaname = 'public'

Create Index

CREATE INDEX index_name
ON table_name (column1, column2, ...);

CREATE INDEX idx_customer_name
ON Customers (customer_name);