PostgreSQL Zero to Advanced #8: Indexes & Performance

Welcome to the advanced section. With 8 users and 14 orders, every query we wrote so far was instant. Real applications have millions of rows, and that is where a query that took 1 ms in development suddenly takes 3 seconds in production. In this part you will learn to read query plans with EXPLAIN ANALYZE and to choose the right index: B-tree, composite, partial and GIN.

Preparing a big table

Our course platform needs page-view tracking: every time a student opens a lesson we log it. Let's create an access_logs table and fill it with 500,000 generated rows using generate_series. Every 500th request is an error (status 500):

CREATE TABLE access_logs (
    id          BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    user_id     INTEGER     NOT NULL REFERENCES users (id),
    course_id   INTEGER     NOT NULL REFERENCES courses (id),
    status_code SMALLINT    NOT NULL,
    viewed_at   TIMESTAMPTZ NOT NULL
);

-- 500,000 fake page views, one per minute starting 2025-01-01
INSERT INTO access_logs (user_id, course_id, status_code, viewed_at)
SELECT (g % 8) + 1,
       (g % 7) + 1,
       CASE WHEN g % 500 = 0 THEN 500 ELSE 200 END,
       timestamptz '2025-01-01 00:00:00+00' + g * interval '1 minute'
FROM generate_series(1, 500000) AS g;

ANALYZE access_logs;

Output:

CREATE TABLE
INSERT 0 500000
ANALYZE

ANALYZE collects statistics about the data (row count, value distribution). The query planner relies on these statistics to choose a plan. Autovacuum runs it automatically, but after a bulk load it is good practice to run it yourself.

SELECT COUNT(*) FROM access_logs;

Output:

 count  
--------
 500000
(1 row)

Reading EXPLAIN

A typical dashboard question: "show the lessons user 3 opened on 1 June 2025". Prefix the query with EXPLAIN to see the plan without running it:

EXPLAIN
SELECT * FROM access_logs
WHERE user_id = 3
  AND viewed_at >= '2025-06-01' AND viewed_at < '2025-06-02';

Output:

                                                                                 QUERY PLAN                                                                                 
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Gather  (cost=1000.00..8340.33 rows=175 width=26)
   Workers Planned: 2
   ->  Parallel Seq Scan on access_logs  (cost=0.00..7322.83 rows=73 width=26)
         Filter: ((viewed_at >= '2025-06-01 00:00:00+00'::timestamp with time zone) AND (viewed_at < '2025-06-02 00:00:00+00'::timestamp with time zone) AND (user_id = 3))
(4 rows)

Read the plan from the most indented line outward:

• Parallel Seq Scan: PostgreSQL reads the entire table (split across 2 parallel workers) and checks each row against the Filter.
• cost=0.00..7322.83: the estimated startup cost and total cost, in arbitrary planner units. Only compare costs between plans; they are not milliseconds.
• rows=175: how many rows the planner expects to return in total.
• width=26: the estimated average row size in bytes.

EXPLAIN ANALYZE: the real numbers

EXPLAIN ANALYZE actually runs the query and adds real timings and row counts:

EXPLAIN ANALYZE
SELECT * FROM access_logs
WHERE user_id = 3
  AND viewed_at >= '2025-06-01' AND viewed_at < '2025-06-02';

Output:

                                                                                 QUERY PLAN                                                                                 
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Gather  (cost=1000.00..8340.33 rows=175 width=26) (actual time=5.961..12.611 rows=180 loops=1)
   Workers Planned: 2
   Workers Launched: 2
   ->  Parallel Seq Scan on access_logs  (cost=0.00..7322.83 rows=73 width=26) (actual time=3.909..9.522 rows=60 loops=3)
         Filter: ((viewed_at >= '2025-06-01 00:00:00+00'::timestamp with time zone) AND (viewed_at < '2025-06-02 00:00:00+00'::timestamp with time zone) AND (user_id = 3))
         Rows Removed by Filter: 166607
 Planning Time: 0.218 ms
 Execution Time: 12.635 ms
(8 rows)

The most important lines: actual ... rows=180 (the planner estimate of 175 was close, which means the statistics are healthy), Rows Removed by Filter: 166607 per worker (about 500,000 rows read to return 180) and Execution Time of roughly 12 ms. Reading half a million rows to keep 180 is wasteful, and it gets linearly worse as the table grows.

Warning: EXPLAIN ANALYZE really executes the statement. For an UPDATE or DELETE, wrap it in BEGIN ... ROLLBACK.

B-tree indexes

An index is a separate, sorted data structure that points to table rows, much like the index at the back of a book. The default type is B-tree, which supports =, <, >, BETWEEN, IN, IS NULL, sorting and prefix LIKE 'abc%'. Let's index viewed_at:

CREATE INDEX idx_access_logs_viewed_at
    ON access_logs (viewed_at);

Output:

CREATE INDEX

EXPLAIN ANALYZE
SELECT * FROM access_logs
WHERE user_id = 3
  AND viewed_at >= '2025-06-01' AND viewed_at < '2025-06-02';

Output:

                                                                       QUERY PLAN                                                                       
--------------------------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using idx_access_logs_viewed_at on access_logs  (cost=0.42..62.06 rows=175 width=26) (actual time=0.017..0.122 rows=180 loops=1)
   Index Cond: ((viewed_at >= '2025-06-01 00:00:00+00'::timestamp with time zone) AND (viewed_at < '2025-06-02 00:00:00+00'::timestamp with time zone))
   Filter: (user_id = 3)
   Rows Removed by Filter: 1260
 Planning Time: 0.388 ms
 Execution Time: 0.143 ms
(6 rows)

From about 12 ms down to about 0.14 ms. The plan is now an Index Scan: PostgreSQL jumps straight to 1 June in the index, reads the 1,440 rows of that day, and still discards 1,260 of them with Filter: (user_id = 3). Good, but we can do better.

Composite indexes

A composite index covers several columns, sorted by the first column, then the second within it, and so on. Because the query filters on both user_id and viewed_at, an index on (user_id, viewed_at) fits perfectly. It also makes the single-column index redundant for this workload, so we drop it (every index costs disk space and slows down writes):

DROP INDEX idx_access_logs_viewed_at;

CREATE INDEX idx_access_logs_user_viewed
    ON access_logs (user_id, viewed_at);

Output:

DROP INDEX
CREATE INDEX

EXPLAIN ANALYZE
SELECT * FROM access_logs
WHERE user_id = 3
  AND viewed_at >= '2025-06-01' AND viewed_at < '2025-06-02';

Output:

                                                                                   QUERY PLAN                                                                                   
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on access_logs  (cost=6.65..583.09 rows=175 width=26) (actual time=0.059..0.111 rows=180 loops=1)
   Recheck Cond: ((user_id = 3) AND (viewed_at >= '2025-06-01 00:00:00+00'::timestamp with time zone) AND (viewed_at < '2025-06-02 00:00:00+00'::timestamp with time zone))
   Heap Blocks: exact=12
   ->  Bitmap Index Scan on idx_access_logs_user_viewed  (cost=0.00..6.61 rows=175 width=0) (actual time=0.047..0.047 rows=180 loops=1)
         Index Cond: ((user_id = 3) AND (viewed_at >= '2025-06-01 00:00:00+00'::timestamp with time zone) AND (viewed_at < '2025-06-02 00:00:00+00'::timestamp with time zone))
 Planning Time: 0.661 ms
 Execution Time: 0.159 ms
(7 rows)

Both conditions now appear in Index Cond and there is no Rows Removed by Filter: the index found exactly the 180 matching rows. On this small dataset the timing is similar, but the amount of work is 8 times smaller, and that difference grows with the data.

The column order matters. An index on (user_id, viewed_at) helps queries that filter on user_id alone, or on user_id plus viewed_at. It is much less useful for a filter on viewed_at alone. Rule of thumb: equality columns first, range or sort columns last.

A bonus: the index is already sorted by viewed_at within each user, so "latest 10 views of user 3" needs no sorting at all:

EXPLAIN ANALYZE
SELECT * FROM access_logs
WHERE user_id = 3
ORDER BY viewed_at DESC
LIMIT 10;

Output:

                                                                            QUERY PLAN                                                                            
------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.42..3.10 rows=10 width=26) (actual time=0.048..0.051 rows=10 loops=1)
   ->  Index Scan Backward using idx_access_logs_user_viewed on access_logs  (cost=0.42..16625.84 rows=62150 width=26) (actual time=0.046..0.049 rows=10 loops=1)
         Index Cond: (user_id = 3)
 Planning Time: 0.337 ms
 Execution Time: 0.064 ms
(5 rows)

Index Scan Backward reads the index from the end and stops after 10 rows. This is the pattern behind every fast "recent activity" feed.

Partial indexes

Sometimes you only ever query a small slice of a table. Our monitoring page only looks at errors (status_code >= 500), which are 0.2% of the rows. A partial index stores only the rows matching its WHERE clause:

CREATE INDEX idx_access_logs_errors
    ON access_logs (viewed_at)
    WHERE status_code >= 500;

Output:

CREATE INDEX

EXPLAIN ANALYZE
SELECT id, user_id, viewed_at
FROM access_logs
WHERE status_code >= 500
  AND viewed_at >= '2025-09-01'
ORDER BY viewed_at
LIMIT 5;

Output:

                                                                  QUERY PLAN                                                                  
----------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.28..0.59 rows=5 width=20) (actual time=0.012..0.018 rows=5 loops=1)
   ->  Index Scan using idx_access_logs_errors on access_logs  (cost=0.28..19.63 rows=306 width=20) (actual time=0.010..0.015 rows=5 loops=1)
         Index Cond: (viewed_at >= '2025-09-01 00:00:00+00'::timestamp with time zone)
 Planning Time: 0.445 ms
 Execution Time: 0.028 ms
(5 rows)

The query's WHERE must imply the index's WHERE for the planner to use it. Look at the size difference:

SELECT indexname,
       pg_size_pretty(pg_relation_size(indexname::regclass)) AS size
FROM pg_indexes
WHERE tablename = 'access_logs'
ORDER BY indexname;

Output:

          indexname          | size  
-----------------------------+-------
 access_logs_pkey            | 11 MB
 idx_access_logs_errors      | 40 kB
 idx_access_logs_user_viewed | 15 MB
(3 rows)

40 kB instead of 11 MB, and inserts of normal (status 200) rows do not touch this index at all. Partial indexes are great for patterns like "unprocessed jobs", "active users" or "non-deleted rows".

GIN indexes for arrays and JSONB

B-tree indexes work on single, comparable values. For "contains" queries on arrays, JSONB and full-text search you need a GIN (Generalized Inverted Index), which maps each element to the rows containing it. Let's give our courses some tags:

ALTER TABLE courses ADD COLUMN tags TEXT[] NOT NULL DEFAULT '{}';

UPDATE courses SET tags = ARRAY['sql', 'database']               WHERE id = 1;
UPDATE courses SET tags = ARRAY['sql', 'postgresql', 'performance'] WHERE id = 2;
UPDATE courses SET tags = ARRAY['javascript', 'frontend']        WHERE id = 3;
UPDATE courses SET tags = ARRAY['javascript', 'react', 'frontend'] WHERE id = 4;
UPDATE courses SET tags = ARRAY['sql', 'database']               WHERE id = 5;
UPDATE courses SET tags = ARRAY['javascript', 'react', 'backend']  WHERE id = 6;
UPDATE courses SET tags = ARRAY['python', 'data']                WHERE id = 7;
UPDATE courses SET tags = ARRAY['docker', 'devops']              WHERE id = 8;

CREATE INDEX idx_courses_tags ON courses USING GIN (tags);

Output:

ALTER TABLE
UPDATE 1
UPDATE 1
UPDATE 1
UPDATE 1
UPDATE 1
UPDATE 1
UPDATE 1
UPDATE 1
CREATE INDEX

The @> operator means "contains":

SELECT title, tags
FROM courses
WHERE tags @> ARRAY['sql'];

Output:

             title             |             tags             
-------------------------------+------------------------------
 SQL Fundamentals              | {sql,database}
 PostgreSQL Performance Tuning | {sql,postgresql,performance}
 Advanced SQL Queries          | {sql,database}
(3 rows)

With only 8 courses, PostgreSQL will (correctly) prefer a sequential scan, because reading one tiny table page is cheaper than using an index. To see that the GIN index works, we temporarily discourage sequential scans in this session. Never do this in production code:

SET enable_seqscan = off;  -- only for this demo session

EXPLAIN
SELECT title FROM courses WHERE tags @> ARRAY['react'];

Output:

SET
                                  QUERY PLAN                                   
-------------------------------------------------------------------------------
 Bitmap Heap Scan on courses  (cost=8.52..12.54 rows=1 width=418)
   Recheck Cond: (tags @> '{react}'::text[])
   ->  Bitmap Index Scan on idx_courses_tags  (cost=0.00..8.52 rows=1 width=0)
         Index Cond: (tags @> '{react}'::text[])
(4 rows)

A Bitmap Index Scan on idx_courses_tags confirms the index is usable. In Part 10 we will use GIN again for JSONB and full-text search.

Common mistakes

• Wrapping the indexed column in a function. An index on viewed_at cannot help WHERE date(viewed_at) = '2025-06-01':

EXPLAIN
SELECT * FROM access_logs
WHERE date(viewed_at) = '2025-06-01';

Output:

                                   QUERY PLAN                                    
---------------------------------------------------------------------------------
 Gather  (cost=1000.00..8052.00 rows=2500 width=26)
   Workers Planned: 2
   ->  Parallel Seq Scan on access_logs  (cost=0.00..6802.00 rows=1042 width=26)
         Filter: (date(viewed_at) = '2025-06-01'::date)
(4 rows)

Rewrite it as a range on the raw column (viewed_at >= '2025-06-01' AND viewed_at < '2025-06-02'), or create an expression index such as CREATE INDEX ON users (lower(email)); for WHERE lower(email) = ....

• Indexing everything. Each index slows down INSERT/UPDATE/DELETE and uses disk. Check pg_stat_user_indexes.idx_scan and drop indexes that are never used.
• Forgetting indexes on foreign keys. PostgreSQL does not index referencing columns automatically. JOINs and ON DELETE checks on orders.user_id need one on big tables.
• Creating indexes on a busy production table without CONCURRENTLY. Plain CREATE INDEX blocks writes until it finishes. Use CREATE INDEX CONCURRENTLY.
• Trusting EXPLAIN on tiny tables. The planner behaves differently on 8 rows than on 8 million. Test with realistic data volumes.
• Stale statistics. If estimated rows differ wildly from actual rows, run ANALYZE.

Exercise

1. Run EXPLAIN ANALYZE SELECT COUNT(*) FROM access_logs WHERE course_id = 5;. Which scan is used? Create an index on course_id and compare.
2. Create indexes on orders (user_id) and orders (course_id) to support the foreign keys from Part 6.
3. Create a partial index that supports "the latest pending orders" (WHERE status = 'pending' ORDER BY ordered_at DESC).
4. Query pg_stat_user_indexes to see how many times each index on access_logs has been scanned.

Summary

You learned to read EXPLAIN and EXPLAIN ANALYZE output, spot sequential scans and "Rows Removed by Filter", and speed up a 500,000-row table with B-tree, composite, partial and GIN indexes. Remember: index for your real query patterns, keep the column order right, and measure before and after.

In Part 9 we make our data safe under concurrency with transactions, isolation levels and row locking, then move business logic into PL/pgSQL functions and triggers.