Mastering Oracle and PostgreSQL Indexes: B-Tree, Bitmap, and When to Use Each

The Fundamental Role of Indexes in Relational Database Performance

Many developers create tables, add primary keys, and notice that the system runs well in the test environment. However, when the application goes to production and reaches millions of rows, queries start to stall. The most common reason for this is the lack of a proper indexing strategy.

An index works much like the index at the back of a technical book: instead of the database having to perform a Full Table Scan (scanning the entire table row by row), it consults the index structure to locate the exact physical block address where the data is stored.

Understanding B-Tree Structure (Balanced Tree)

The default index type in virtually all commercial relational systems (Oracle, PostgreSQL, MySQL/InnoDB) is the B-Tree. It organizes data into a balanced tree of blocks, allowing searches, insertions, and deletions in logarithmic time O(log n).

B-Trees are extremely efficient for high‑cardinality columns—that is, columns whose values are highly unique or have many distinct variations, such as:

• CPFs or CNPJs (Brazilian tax IDs)
• Foreign keys
• User emails
• Transaction or protocol codes

Example of creating a B-Tree index:

CREATE INDEX idx_clients_email ON clients(email);

When to Use Bitmap Indexes (Oracle-specific / Data Warehouses)

While B-Trees shine on high‑cardinality columns, Bitmap indexes are designed specifically for low‑cardinality scenarios and large‑volume analytical environments (Data Warehouses), where there are few distinct values repeated thousands of times.

Classic examples of ideal columns for Bitmap:

• Order status ('PENDING', 'APPROVED', 'CANCELLED')
• Gender or age range of customers
• Boolean indicators (Yes/No, Active/Inactive)

Bitmap stores each distinct value associated with a bit map (bits of 0 or 1 corresponding to the physical row in the table). This allows complex logical operations (AND, OR, NOT) to be resolved ultra-fast using CPU bitwise operations.

Example of creating a Bitmap index:

CREATE BITMAP INDEX idx_orders_status ON orders(status_code);

Important note: Avoid using Bitmap indexes in OLTP transactional tables that face heavy concurrent writes (INSERT, UPDATE), because bitmap‑level locking can cause severe contention bottlenecks.

The Hidden Cost of Indexes

There is a common myth in software development that "the more indexes, the faster the system becomes". This is totally false. Every index has a maintenance cost.

Whenever an INSERT, UPDATE (on indexed columns), or DELETE is performed on a table, the database not only changes the row in the main table; it is also forced to structurally update all associated indexes to maintain tree or bitmap balance.

Maintenance best practices:

• Audit unused indexes: Monitor system views (like USER_UNUSED_INDEXES in Oracle) to identify indexes that are never traversed by the optimizer and are consuming disk space and write I/O.
• Be careful with column order in composite indexes: In a multi‑column index (col_a, col_b), order matters. Queries that filter only on col_b will not be able to use the index tree efficiently.

Analyzing the Execution Plan

Never create an index "in the dark". Always use your DBMS's execution plan tool to verify that the optimizer actually chooses to use the index you created.

-- Example basic command in Oracle to generate the execution plan EXPLAIN PLAN FOR SELECT * FROM clients WHERE email = 'contato@devjorge.com.br'; SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

If the plan returns a TABLE ACCESS FULL instead of an INDEX RANGE SCAN or INDEX UNIQUE SCAN for a highly selective query, it may be a sign that table statistics are outdated or the data volume is still too small to offset the index read cost.