How to create an index in PostgreSQL

Create a PostgreSQL index after a query plan shows a selective filter, join, or sort reading more rows than the statement needs. The index should match the SQL predicate closely enough for the planner to choose an index-backed path instead of another full table scan.

The default B-tree index supports equality and range predicates and can also satisfy ordering on the indexed columns. Multi-column indexes depend on column order, so equality predicates usually belong before range or ordering columns, while GIN, GiST, BRIN, expression, and partial indexes belong to more specific operators or row subsets.

Each index consumes disk space and adds work to INSERT, UPDATE, and DELETE statements because PostgreSQL maintains index entries alongside table rows. A normal CREATE INDEX locks out writes on the target table until the build completes, while CREATE INDEX CONCURRENTLY allows concurrent writes but takes longer, performs extra scans, and cannot run inside an explicit transaction block. EXPLAIN (ANALYZE, BUFFERS) executes the query, so run it carefully on expensive statements or busy production systems.

1. Inspect the target query plan before changing the schema.

   $ 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..2084.00 rows=100 width=34) (actual time=0.010..6.207 rows=100.00 loops=1)
      Filter: (customer_id = 42)
      Rows Removed by Filter: 99900
      Buffers: shared hit=834
    Planning:
      Buffers: shared hit=72
    Planning Time: 0.179 ms
    Execution Time: 6.245 ms
   (8 rows)

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

2. Choose index columns and order from the query predicates that need the new access path.

   For multi-column B-tree indexes, the leftmost columns matter most; place equality predicates first, then range or ordering columns that the same query uses.

3. Create the index with CONCURRENTLY when the table must keep accepting writes during the build.

   $ 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 from current table data.

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

   Related: How to vacuum and analyze tables in PostgreSQL

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=5.07..290.13 rows=100 width=34) (actual time=0.023..0.245 rows=100.00 loops=1)
      Recheck Cond: (customer_id = 42)
      Heap Blocks: exact=100
      Buffers: shared hit=102
      ->  Bitmap Index Scan on idx_orders_customer_id  (cost=0.00..5.04 rows=100 width=0) (actual time=0.009..0.009 rows=100.00 loops=1)
            Index Cond: (customer_id = 42)
            Index Searches: 1
            Buffers: shared hit=2
    Planning:
      Buffers: shared hit=101 read=1
    Planning Time: 0.270 ms
    Execution Time: 0.279 ms
   (12 rows)

6. Review index usage counters after representative queries have run.

   $ sudo -u postgres psql -d appdb -c "SELECT s.indexrelname AS index, 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          | idx_scan | idx_tup_read | idx_tup_fetch 
   ------------------------+----------+--------------+---------------
    idx_orders_customer_id |        1 |          100 |             0
    orders_pkey            |        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.