Indexes

An index is a separate, sorted structure that lets PostgreSQL find rows without reading the whole table. EXPLAIN ANALYZE shows whether it was used and what it saved. These examples need enough rows for that to matter, so the setup generates 200,000 orders.

-- setup.sql
CREATE TABLE orders AS
SELECT g                                        AS id,
       (g % 5000) + 1                           AS customer_id,
       CASE WHEN g % 100 = 0 THEN 'pending' ELSE 'shipped' END AS status,
       'User' || (g % 5000) || '@Example.com'   AS email,
       DATE '2024-01-01' + (g % 365)            AS created_on,
       (g % 1000) / 10.0                        AS amount
FROM generate_series(1, 200000) AS g;
ALTER TABLE orders ADD PRIMARY KEY (id);
VACUUM ANALYZE orders;

$ psql -d sqldemo -f setup.sql
SELECT 200000
ALTER TABLE
VACUUM

Without an Index

Find one customer’s orders when only the primary key is indexed.

-- no_index.sql
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF)
SELECT id, amount FROM orders WHERE customer_id = 42;

$ psql -d sqldemo -f no_index.sql
                 QUERY PLAN                  
---------------------------------------------
 Seq Scan on orders (actual rows=40 loops=1)
   Filter: (customer_id = 42)
   Rows Removed by Filter: 199960
 Planning Time: 0.160 ms
 Execution Time: 8.534 ms
(5 rows)

Seq Scan means PostgreSQL read every row. Rows Removed by Filter: 199960 is the waste: it checked 200,000 rows to return 40. TIMING OFF hides per-step timers, but the summary still reports the total execution time.

A B-Tree Index

CREATE INDEX builds the default index type, a B-tree.

-- btree.sql
CREATE INDEX orders_customer_idx ON orders (customer_id);
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF)
SELECT id, amount FROM orders WHERE customer_id = 42;

$ psql -d sqldemo -f btree.sql
CREATE INDEX
                               QUERY PLAN                                
-------------------------------------------------------------------------
 Bitmap Heap Scan on orders (actual rows=40 loops=1)
   Recheck Cond: (customer_id = 42)
   Heap Blocks: exact=40
   ->  Bitmap Index Scan on orders_customer_idx (actual rows=40 loops=1)
         Index Cond: (customer_id = 42)
 Planning Time: 0.552 ms
 Execution Time: 0.204 ms
(7 rows)

The plan now starts with Bitmap Index Scan: the index found the 40 matching row locations directly, and Heap Blocks: exact=40 shows only 40 table pages were read. Compare the execution time with the sequential scan above. The exact numbers depend on your machine, but the gap is the point.

The Function Trap

An index on email stores the original text. A query on lower(email) asks a different question.

-- function_trap.sql
CREATE INDEX orders_email_idx ON orders (email);
EXPLAIN (COSTS OFF)
SELECT count(*) FROM orders WHERE lower(email) = 'user42@example.com';
CREATE INDEX orders_email_lower_idx ON orders (lower(email));
EXPLAIN (COSTS OFF)
SELECT count(*) FROM orders WHERE lower(email) = 'user42@example.com';

$ psql -d sqldemo -f function_trap.sql
CREATE INDEX
                                 QUERY PLAN                                  
-----------------------------------------------------------------------------
 Finalize Aggregate
   ->  Gather
         Workers Planned: 1
         ->  Partial Aggregate
               ->  Parallel Index Only Scan using orders_email_idx on orders
                     Filter: (lower(email) = 'user42@example.com'::text)
(6 rows)

CREATE INDEX
                              QUERY PLAN                               
-----------------------------------------------------------------------
 Aggregate
   ->  Bitmap Heap Scan on orders
         Recheck Cond: (lower(email) = 'user42@example.com'::text)
         ->  Bitmap Index Scan on orders_email_lower_idx
               Index Cond: (lower(email) = 'user42@example.com'::text)
(5 rows)

The first plan does touch orders_email_idx, but look at the line under it: Filter, not Index Cond. PostgreSQL read every entry in the index and lowercased each one, because a sorted list of 'User42@Example.com' values cannot be searched for 'user42@example.com'. The expression index on lower(email) stores the lowercased values, and the second plan searches it with a real Index Cond.

Multi-Column Index Column Order

A multi-column index is sorted by its first column, then by the second within each first value. BUFFERS counts the 8 kB pages each query read.

-- column_order.sql
CREATE INDEX orders_status_date_idx ON orders (status, created_on);
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT count(*) FROM orders WHERE status = 'pending' AND created_on = '2024-03-01';
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT count(*) FROM orders WHERE created_on = '2024-03-01';

$ psql -d sqldemo -f column_order.sql
CREATE INDEX
                                       QUERY PLAN                                       
----------------------------------------------------------------------------------------
 Aggregate (actual rows=1 loops=1)
   Buffers: shared hit=1 read=2
   ->  Index Only Scan using orders_status_date_idx on orders (actual rows=27 loops=1)
         Index Cond: ((status = 'pending'::text) AND (created_on = '2024-03-01'::date))
         Heap Fetches: 0
         Buffers: shared hit=1 read=2
 Planning:
   Buffers: shared hit=104 read=1
(8 rows)

                                       QUERY PLAN                                       
----------------------------------------------------------------------------------------
 Aggregate (actual rows=1 loops=1)
   Buffers: shared hit=3 read=184
   ->  Index Only Scan using orders_status_date_idx on orders (actual rows=548 loops=1)
         Index Cond: (created_on = '2024-03-01'::date)
         Heap Fetches: 0
         Buffers: shared hit=3 read=184
(6 rows)

Both plans say Index Only Scan with an Index Cond, so the second looks fine at a glance. The buffer counts tell the truth: the first query jumped to pending + March 1 and read 3 pages; the second had no status to jump to and read the entire index, 187 pages. Put the column you always filter on first.

Partial Indexes

Only 1% of orders are pending. A partial index stores just those rows.

-- partial.sql
CREATE INDEX orders_status_full_idx ON orders (status);
CREATE INDEX orders_pending_idx ON orders (id) WHERE status = 'pending';
SELECT indexrelname AS index, pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE indexrelname IN ('orders_status_full_idx', 'orders_pending_idx');
EXPLAIN (COSTS OFF)
SELECT id FROM orders WHERE status = 'pending' ORDER BY id LIMIT 5;

$ psql -d sqldemo -f partial.sql
CREATE INDEX
CREATE INDEX
         index          |  size   
------------------------+---------
 orders_status_full_idx | 1368 kB
 orders_pending_idx     | 64 kB
(2 rows)

                        QUERY PLAN                        
----------------------------------------------------------
 Limit
   ->  Index Only Scan using orders_pending_idx on orders
(2 rows)

The partial index is about a twentieth of the size of the full one, and the planner chose it for the pending-orders query because the query’s WHERE matches the index’s WHERE. A work queue that only ever looks at unfinished rows is the classic use.

Covering Indexes With INCLUDE

If every column a query needs is in the index, PostgreSQL can skip the table entirely.

-- covering.sql
CREATE INDEX orders_cust_incl_idx ON orders (customer_id) INCLUDE (amount);
VACUUM orders;
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT customer_id, amount FROM orders WHERE customer_id = 42;

$ psql -d sqldemo -f covering.sql
CREATE INDEX
VACUUM
                                  QUERY PLAN                                   
-------------------------------------------------------------------------------
 Index Only Scan using orders_cust_incl_idx on orders (actual rows=40 loops=1)
   Index Cond: (customer_id = 42)
   Heap Fetches: 0
(3 rows)

Index Only Scan with Heap Fetches: 0 means no table pages were read at all. INCLUDE stores amount in the index without making it part of the sort key. The VACUUM matters: an index-only scan still visits the table for rows whose pages are not yet marked all-visible.

GIN Indexes for Arrays and JSONB

B-trees index whole values. A GIN index indexes the elements inside arrays and JSONB documents.

-- gin.sql
CREATE TABLE articles AS
SELECT g AS id,
       ARRAY['sql', CASE WHEN g % 50 = 0 THEN 'postgres' ELSE 'mysql' END] AS tags
FROM generate_series(1, 50000) AS g;
CREATE INDEX articles_tags_idx ON articles USING gin (tags);
ANALYZE articles;
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT count(*) FROM articles WHERE tags @> ARRAY['postgres'];

$ psql -d sqldemo -f gin.sql
SELECT 50000
CREATE INDEX
ANALYZE
                                  QUERY PLAN                                   
-------------------------------------------------------------------------------
 Aggregate (actual rows=1 loops=1)
   ->  Bitmap Heap Scan on articles (actual rows=1000 loops=1)
         Recheck Cond: (tags @> '{postgres}'::text[])
         Heap Blocks: exact=468
         ->  Bitmap Index Scan on articles_tags_idx (actual rows=1000 loops=1)
               Index Cond: (tags @> '{postgres}'::text[])
(6 rows)

@> means “contains”. The GIN index went straight to the 1,000 articles tagged postgres. The same index type powers JSONB containment queries and full-text search; GiST and hash indexes cover geometric/range types and pure equality, and are needed far less often.

Indexes Cost Writes

Every index must be updated on every insert. orders now has eight indexes; plain_copy has none. psql’s \timing measures both loads of the same 200,000 rows.

-- write_cost.sql
\timing on
CREATE TABLE plain_copy AS SELECT * FROM orders WITH NO DATA;
INSERT INTO plain_copy SELECT * FROM orders;
INSERT INTO orders (id, customer_id, status, email, created_on, amount)
SELECT id + 200000, customer_id, status, email, created_on, amount FROM orders;

$ psql -d sqldemo -f write_cost.sql
Timing is on.
CREATE TABLE AS
Time: 2.329 ms
INSERT 0 200000
Time: 95.018 ms
INSERT 0 200000
Time: 2254.587 ms (00:02.255)

The same rows took many times longer to insert into the indexed table. The exact ratio depends on your disk, memory, and settings; another run on different hardware measured about 8x rather than the gap shown here, but the indexed insert is always the slower one. An index pays off only when queries use it often enough to justify slowing every write.

Practice

1. Run the lowercase-email and pending-orders queries for real, then list the indexes on orders that no query has used yet.

-- practice_unused.sql
SELECT count(*) FROM orders WHERE lower(email) = 'user42@example.com';
SELECT count(*) FROM (SELECT id FROM orders WHERE status = 'pending'
                      ORDER BY id LIMIT 5) t;
SELECT pg_stat_force_next_flush();
SELECT indexrelname AS index, idx_scan
FROM pg_stat_user_indexes
WHERE relname = 'orders' AND idx_scan = 0
ORDER BY indexrelname;

$ psql -d sqldemo -f practice_unused.sql
 count 
-------
    80
(1 row)

 count 
-------
     5
(1 row)

 pg_stat_force_next_flush 
--------------------------
 
(1 row)

         index          | idx_scan 
------------------------+----------
 orders_email_idx       |        0
 orders_pkey            |        0
 orders_status_full_idx |        0
(3 rows)

2. Make the created_on-only query from column_order.sql cheap, and prove it with BUFFERS.

-- practice_date_idx.sql
CREATE INDEX orders_created_idx ON orders (created_on);
VACUUM orders;
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT count(*) FROM orders WHERE created_on = '2024-03-01';

$ psql -d sqldemo -f practice_date_idx.sql
CREATE INDEX
VACUUM
                                     QUERY PLAN                                      
-------------------------------------------------------------------------------------
 Aggregate (actual rows=1 loops=1)
   Buffers: shared hit=1 read=3
   ->  Index Only Scan using orders_created_idx on orders (actual rows=1096 loops=1)
         Index Cond: (created_on = '2024-03-01'::date)
         Heap Fetches: 0
         Buffers: shared hit=1 read=3
 Planning:
   Buffers: shared hit=50
(8 rows)

3. Drop the two unhelpful indexes from Practice 1 and confirm they are gone.

-- practice_drop.sql
DROP INDEX orders_status_full_idx, orders_email_idx;
SELECT count(*) AS indexes_left FROM pg_indexes WHERE tablename = 'orders';

$ psql -d sqldemo -f practice_drop.sql
DROP INDEX
 indexes_left 
--------------
            7
(1 row)