The most common task is creating a single-column B-tree, for example CREATE INDEX idx_users_email ON users (email). Adding the UNIQUE keyword makes the index enforce uniqueness, and PostgreSQL automatically creates one whenever you declare a column UNIQUE or as a PRIMARY KEY. Composite B-tree indexes list multiple columns in CREATE INDEX orders (customer_id, order_date), and column order matters profoundly because keys are sorted lexicographically: the index is only usable for predicates on a leading prefix. So an index on (a, b, c) serves a query filtering on a alone, on a plus a range on b, or on equality across all three, but it cannot serve a query that filters only on b or only on c.
Two refinements extend what a B-tree can cover. The INCLUDE clause adds extra columns to the leaf pages without making them part of the search key, so they do not affect ordering or uniqueness rules; this is how you build a covering index that allows the planner to satisfy a query entirely from index pages. Expression indexes go further by indexing the result of a function, such as CREATE INDEX idx ON t (lower(email)), and they are invisible to the planner unless the query uses the exact same expression. Partial indexes add a WHERE clause to restrict the rows indexed, yielding smaller, faster, and less frequently updated indexes for sparse predicates like status = 'pending' or deleted_at IS NULL.
Two more B-tree details are worth noting. Index-only scans read everything directly from the index when the visibility map shows the required heap pages are all-visible and all referenced columns are indexed or INCLUDE-d, eliminating the heap fetch altogether. For LIKE and ILIKE with non-C locales, B-tree indexes built with the text_pattern_ops or bpchar_pattern_ops opclass are required; otherwise the locale-aware comparison prevents the optimizer from using the index for prefix patterns. A GIN index built with the gin_trgm_ops opclass serves the related case of LIKE '%foo%' substring matching, while a plain B-tree on a prefix column handles ILIKE 'foo%'.