Fix the Slow Query

• Populate a table with 5 million rows.
• Run a representative query and observe poor performance.
• Inspect the query plan to identify the bottleneck.
• Add an appropriate index.
• Re-run the query and observe the performance improvement.

​

At-a-glance: What you will run

​

1) Create the table

CREATE TABLE IF NOT EXISTS events (
  id bigserial PRIMARY KEY,
  user_id integer NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now(),
  payload text
);

​

2) Populate the table with 5 million rows

INSERT INTO events (user_id, created_at, payload)
SELECT (i % 100000) + 1,
       now() - (i % 365)::int * interval '1 day',
       'payload ' || i::text
FROM generate_series(1, 5000000) AS s(i);

• One-statement bulk inserts avoid round-trips and are fast.
• The modulus on user_id creates repeated values useful for selective queries.

ANALYZE events;

​

3) Run the baseline query that will be slow

EXPLAIN ANALYZE
SELECT count(*) FROM events WHERE user_id = 12345;

Seq Scan on events  (cost=0.00..120000.00 rows=50000 width=8) (actual time=10000.000..10000.500 rows=50 loops=1)
  Filter: (user_id = 12345)
  Rows Removed by Filter: 4999950
Planning Time: 0.500 ms
Execution Time: 10000.600 ms

​

4) Diagnose why the query is slow

• A sequential scan reads every row because no index exists to target matching user_id values.
• Use EXPLAIN / EXPLAIN ANALYZE to confirm scan type and estimated row counts.
• If the predicate is selective (matches a small fraction of rows), an index will reduce I/O dramatically.

• EXPLAIN / EXPLAIN ANALYZE — PostgreSQL EXPLAIN documentation
• ANALYZE — PostgreSQL ANALYZE documentation

​

5) Create an index on the filtered column

CREATE INDEX CONCURRENTLY idx_events_user_id ON events (user_id);

• CONCURRENTLY prevents write-locking the table during index build (recommended in production). It must run outside an explicit transaction.
• For a local test where you can afford a quick lock, you may omit CONCURRENTLY for a faster build:
  CREATE INDEX idx_events_user_id ON events (user_id);
  
• After index creation, run:
  ANALYZE events;
  
  PostgreSQL often updates stats during a concurrent build, but an explicit ANALYZE is safe.

​

6) Re-run the query and compare performance

EXPLAIN ANALYZE
SELECT count(*) FROM events WHERE user_id = 12345;

Index Scan using idx_events_user_id on events  (cost=0.29..50.00 rows=50 width=8) (actual time=0.100..0.200 rows=50 loops=1)
  Index Cond: (user_id = 12345)
Planning Time: 0.300 ms
Execution Time: 0.250 ms

• If the planner chooses an Index Only Scan, that indicates the index contains all required columns and PostgreSQL avoided accessing the heap.
• Performance gains depend on selectivity, caching, and the storage layout of your table.

​

7) Wrap-up, tips, and best practices

• Always use EXPLAIN / EXPLAIN ANALYZE to verify the planner’s decisions before and after adding indexes.
• Indexes improve read performance but increase write latency and storage use—index only what is necessary.
• For queries filtering on multiple columns, consider multi-column or composite indexes that match the WHERE clause.
• On heavy write workloads, prefer CREATE INDEX CONCURRENTLY to avoid blocking writes.
• For frequent COUNT(*) queries over large tables, consider summary tables, materialized views, or approximate-count solutions instead of repeatedly scanning millions of rows.

• PostgreSQL Documentation — CREATE INDEX
• PostgreSQL Documentation — EXPLAIN
• PostgreSQL Documentation — ANALYZE

DROP INDEX IF EXISTS idx_events_user_id;
TRUNCATE TABLE events;