Indexes in PostgreSQL: B-tree, GIN, and When to Use Them

1. Overview

PostgreSQL supports many index types, but B-tree and GIN are the two you’ll encounter most frequently in daily work. Choosing the wrong index type makes queries slow or wastes space without providing any benefit.

This article explains how B-tree and GIN work at a practical level, how to create indexes correctly, how to read EXPLAIN to verify an index is actually being used, and a checklist for choosing the right index type. After reading, you’ll confidently add indexes to Order and Product tables without guessing.

2. B-tree Index: Default and Most Common

2.1. How B-tree Works

B-tree (balanced tree) stores values in sorted order, enabling fast search, comparison (<, >, BETWEEN), and sorting (ORDER BY)—with O(log n) complexity. This is the default index type when you run CREATE INDEX without specifying a method.

CREATE TABLE customer_order (
    id BIGSERIAL PRIMARY KEY,
    customer_id BIGINT NOT NULL,
    status VARCHAR(20) NOT NULL,
    created_at TIMESTAMP NOT NULL,
    total_amount NUMERIC(12,2)
);

CREATE INDEX idx_order_customer_id ON customer_order (customer_id);

The CREATE INDEX line creates a B-tree on the customer_id column. When you query WHERE customer_id = 123, PostgreSQL traverses the tree instead of scanning the entire table (sequential scan).

2.2. Verifying an Index Is Actually Used

EXPLAIN ANALYZE
SELECT * FROM customer_order WHERE customer_id = 123;

Index Scan using idx_order_customer_id on customer_order
  Index Cond: (customer_id = 123)
  Planning Time: 0.123 ms
  Execution Time: 0.045 ms

Index Scan means the planner chose to use the index. If you see Seq Scan despite having an index, it’s usually because the table is too small (the planner thinks a full scan is cheaper) or statistics are stale—run ANALYZE customer_order; to update them.

2.3. Composite Indexes and Column Order

B-tree supports multiple columns, but column order determines effectiveness.

CREATE INDEX idx_order_customer_status
    ON customer_order (customer_id, status);

-- Uses the index fully
SELECT * FROM customer_order
WHERE customer_id = 123 AND status = 'PAID';

-- Only uses the customer_id part of the index
SELECT * FROM customer_order WHERE customer_id = 123;

-- CANNOT use this index (missing customer_id)
SELECT * FROM customer_order WHERE status = 'PAID';

The principle: a composite index is like an address book sorted by (customer_id, status)—you can look up by customer_id first, but you can’t directly look up by status alone.

Note: Place the column with high selectivity (high cardinality, many distinct values) first if columns are used independently in queries. If you always query both columns together, put the most selective column first so the tree narrows the range quickly.

3. GIN Index: For “Multiple Values Per Row” Data

3.1. When B-tree Falls Short

B-tree is inefficient with data types where one row contains multiple “elements” to search independently: arrays (array), JSON (jsonb), full-text search (tsvector). GIN (Generalized Inverted Index) was designed for this—it indexes each element separately, not the entire value.

3.2. GIN for JSONB

CREATE TABLE product (
    id BIGSERIAL PRIMARY KEY,
    name VARCHAR(200) NOT NULL,
    attributes JSONB NOT NULL
);

CREATE INDEX idx_product_attributes ON product USING GIN (attributes);

INSERT INTO product (name, attributes) VALUES
('Laptop Dell', '{"brand": "Dell", "ram_gb": 16, "tags": ["laptop", "office"]}'),
('Laptop HP',   '{"brand": "HP", "ram_gb": 8, "tags": ["laptop", "gaming"]}');

SELECT * FROM product WHERE attributes @> '{"brand": "Dell"}';
SELECT * FROM product WHERE attributes -> 'tags' @> '["gaming"]';

USING GIN (attributes) creates an index on the entire JSONB column. The @> (“contains”) operator is what GIN optimizes best—it checks whether the JSON on the left contains the structure on the right without parsing the entire document each time.

Bitmap Heap Scan on product
  Recheck Cond: (attributes @> '{"brand": "Dell"}'::jsonb)
  ->  Bitmap Index Scan on idx_product_attributes
        Index Cond: (attributes @> '{"brand": "Dell"}'::jsonb)

3.3. GIN for Full-Text Search

ALTER TABLE product ADD COLUMN search_vector tsvector
    GENERATED ALWAYS AS (to_tsvector('simple', name)) STORED;

CREATE INDEX idx_product_search ON product USING GIN (search_vector);

SELECT name FROM product
WHERE search_vector @@ to_tsquery('simple', 'laptop & dell');

The search_vector column is a generated column that automatically produces a tsvector from name. A GIN index on this column makes @@ (match) fast with large text data, instead of LIKE '%laptop%' requiring a full table scan.

3.4. GIN for Arrays

CREATE TABLE article (
    id BIGSERIAL PRIMARY KEY,
    title VARCHAR(200),
    tag_list TEXT[]
);

CREATE INDEX idx_article_tags ON article USING GIN (tag_list);

SELECT * FROM article WHERE tag_list @> ARRAY['spring-boot'];

Like JSONB, GIN indexes each element in the tag_list array, so querying “articles containing tag X” is many times faster than B-tree on large tables.

4. Comparison and Costs

Criteria	B-tree	GIN
Best for	scalar: numbers, strings, dates	array, jsonb, tsvector
Supported operators	=, <, >, BETWEEN, ORDER BY	@>, ?, @@, &&
Write speed (INSERT/UPDATE)	fast	slower, especially for large JSONB UPDATEs
Index size	smaller	larger (stores each element)
Build time	fast	slower on large tables

GIN is more expensive to write because each INSERT/UPDATE must update multiple entries (one for each element in JSON/array), not just one entry like B-tree.

-- Check actual index size
SELECT indexname, pg_size_pretty(pg_relation_size(indexname::regclass))
FROM pg_indexes
WHERE tablename = 'product';

5. Testing to Confirm Index Is Used (Spring Boot Integration)

When working with Spring Data JPA, you usually want to verify the index works before deployment. Use Testcontainers to test against real PostgreSQL.

<dependency>
    <groupId>org.testcontainers</groupId>
    <artifactId>postgresql</artifactId>
    <version>1.20.1</version>
    <scope>test</scope>
</dependency>

@Testcontainers
@SpringBootTest
class ProductIndexTest {

    @Container
    static PostgreSQLContainer<?> postgres =
            new PostgreSQLContainer<>("postgres:16-alpine");

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Test
    void shouldUseGinIndexForJsonbQuery() {
        jdbcTemplate.execute("""
            INSERT INTO product (name, attributes)
            VALUES ('Laptop Dell', '{"brand": "Dell"}')
        """);

        String plan = jdbcTemplate.queryForList(
                "EXPLAIN SELECT * FROM product WHERE attributes @> '{\"brand\": \"Dell\"}'"
        ).stream()
         .map(row -> row.get("QUERY PLAN").toString())
         .reduce("", (a, b) -> a + b);

        assertThat(plan).containsIgnoringCase("idx_product_attributes");
    }
}

This test executes EXPLAIN (no need for ANALYZE), concatenates the result lines into a string, then checks whether the plan mentions the expected index name. This confirms the index actually participates in the query plan, not just “exists in the schema”.

Note: EXPLAIN doesn’t run the query, just asks the planner. To see actual execution time, use EXPLAIN ANALYZE—but remember it executes the statement, so avoid it with DELETE/UPDATE unless wrapped in a transaction rollback.

6. Index Selection Checklist

• Filtering by scalar column (numbers, strings, dates) with =, <, >, or needing ORDER BY → B-tree.
• Filtering multiple columns at once with “AND” logic → composite B-tree, place high-selectivity or independently-used columns first.
• Querying JSONB column with @>, ?, ?&, ?| → GIN.
• Full-text search (@@ with tsvector) → GIN.
• Querying arrays with @>, &&, <@ → GIN.
• High-insert table, low-read, frequently-changing JSONB → consider not indexing the entire column with GIN, only index necessary sub-expressions (expression index).

-- Expression index: only index a specific key in JSONB, lighter than full-column GIN
CREATE INDEX idx_product_brand ON product ((attributes ->> 'brand'));

This expression index is actually a B-tree on the result of attributes ->> 'brand' (text value), suitable when you always query by exactly the same fixed key, without needing GIN’s flexibility.

7. Common Mistakes

• Indexing a low-cardinality column (e.g., status with only 3 values on a million-row table): the planner usually skips the index and chooses Seq Scan because it’s cheaper. Having an index doesn’t mean it will be used.
• Forgetting ANALYZE after inserting large data: the planner relies on stale statistics, estimates row counts incorrectly, chooses a bad plan.
• Using GIN for a column that rarely changes but gets written many times: the cumulative cost of updating the index outweighs query benefits—benchmark with EXPLAIN ANALYZE before deciding.
• Composite B-tree with wrong column order: putting rarely-used columns first makes the index nearly useless for real queries.
• Creating too many indexes on one table: each INSERT/UPDATE must update all related indexes, slowing the write path even if not all indexes are used.

8. Conclusion

B-tree is the default choice, suitable for most filtering and sorting conditions on scalar data. GIN is reserved for “multiple-value” data like JSONB, arrays, full-text—where B-tree cannot represent efficiently.

Before adding an index, always check with EXPLAIN ANALYZE to see if the planner actually uses it, and measure write cost if the table has high INSERT/UPDATE frequency. Indexes aren’t “just add it to be safe”—each index is a trade-off between read speed and write speed.