How to create an index in PostgreSQL

Indexes in PostgreSQL reduce query latency by avoiding full table scans on large tables, which keeps application response time stable as row counts grow and workloads scale.

PostgreSQL chooses execution plans based on table and index statistics, selecting between Seq Scan, Index Scan, and Bitmap Index Scan paths depending on estimated cost. The default B-tree index supports equality and range predicates and can satisfy ordering on the indexed columns, while other index types (such as GIN, GiST, and BRIN) target specific operators and data types.

Each index consumes disk space and adds overhead to INSERT, UPDATE, and DELETE because index entries must be maintained alongside table rows. Creating an index without CONCURRENTLY blocks writes to the target table until the build completes, while CONCURRENTLY reduces write blocking at the cost of longer runtime and extra scans; CREATE INDEX CONCURRENTLY also cannot run inside an explicit transaction block. EXPLAIN (ANALYZE) executes the query, so running it against expensive statements during peak load can cause noticeable I/O and CPU pressure.

1. Inspect the target query plan for a Seq Scan, heavy buffer reads, or expensive Sort nodes.

   $ sudo -u postgres psql -d appdb -c "EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE customer_id = 42;"
                                                QUERY PLAN                                             
   ----------------------------------------------------------------------------------------------------
    Seq Scan on orders  (cost=0.00..11.25 rows=10 width=33) (actual time=0.011..0.049 rows=10 loops=1)
      Filter: (customer_id = 42)
      Rows Removed by Filter: 490
      Buffers: shared read=5
    Planning:
      Buffers: shared hit=69 read=3
    Planning Time: 0.201 ms
    Execution Time: 0.074 ms
   (8 rows)

   A Seq Scan is normal when a large fraction of rows match the predicate, even when an index exists.

2. Select index columns and order to match the most common WHERE, JOIN, and ORDER BY clauses.

   For multi-column B-tree indexes, the leftmost columns matter most; place high-selectivity equality predicates first, then range or ordering columns.

3. Create the index using CREATE INDEX with CONCURRENTLY on busy tables.

   $ sudo -u postgres psql -d appdb -c "CREATE INDEX CONCURRENTLY idx_orders_customer_id ON orders (customer_id);"
   CREATE INDEX

   Omitting CONCURRENTLY blocks writes to the table for the duration of the build.

4. Refresh table statistics after the index is created so the planner can cost the new path accurately.

   $ sudo -u postgres psql -d appdb -c "ANALYZE orders;"
   ANALYZE

5. Verify the planner selects an Index Scan or Bitmap Index Scan for the target query.

   $ sudo -u postgres psql -d appdb -c "EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE customer_id = 42;"
                                                              QUERY PLAN                                                            
   ---------------------------------------------------------------------------------------------------------------------------------
    Bitmap Heap Scan on orders  (cost=4.23..9.35 rows=10 width=33) (actual time=0.010..0.013 rows=10 loops=1)
      Recheck Cond: (customer_id = 42)
      Heap Blocks: exact=4
      Buffers: shared hit=5
      ->  Bitmap Index Scan on idx_orders_customer_id  (cost=0.00..4.22 rows=10 width=0) (actual time=0.006..0.006 rows=10 loops=1)
            Index Cond: (customer_id = 42)
            Buffers: shared hit=1
    Planning:
      Buffers: shared hit=103 read=1
    Planning Time: 0.266 ms
    Execution Time: 0.038 ms
   (11 rows)

6. Review index size and usage counters to catch unused or low-value indexes early.

   $ sudo -u postgres psql -d appdb -c "SELECT s.indexrelname AS index, pg_size_pretty(pg_relation_size(s.indexrelid)) AS size, s.idx_scan, s.idx_tup_read, s.idx_tup_fetch FROM pg_stat_user_indexes s WHERE s.schemaname = 'public' AND s.relname = 'orders' ORDER BY s.idx_scan DESC;"
            index          | size  | idx_scan | idx_tup_read | idx_tup_fetch 
   ------------------------+-------+----------+--------------+---------------
    idx_orders_customer_id | 16 kB |        1 |           10 |             0
    orders_pkey            | 32 kB |        0 |            0 |             0
   (2 rows)

   Statistics reset on server restart or manual reset, so compare trends over a meaningful window instead of treating idx_scan as a lifetime counter.