How to Add an Index to Speed Up a Slow Postgres Query

Why a 10,000-row table can be slow

A table with 10,000 rows is tiny by database standards, but Postgres still reads every single row when there is no index on the column you are filtering by. That is a sequential scan - the database cannot skip to the matching rows, so it reads them all before discarding the ones that don't match.

A search query like this will trigger a seq scan if name has no index:

SELECT * FROM products WHERE LOWER(name) LIKE 'widget%';

At 10,000 rows that costs milliseconds. At 1 million rows it costs seconds. Index it now, before the table grows.

Step 1: confirm the seq scan with EXPLAIN ANALYZE

Connect to Postgres and prefix the slow query with EXPLAIN ANALYZE:

EXPLAIN ANALYZE
SELECT * FROM products WHERE LOWER(name) LIKE 'widget%';

Look for Seq Scan on products in the plan output. The rows= and actual time= values show how many rows were read and how long it took. A seq scan with a high row count is the signal to add an index.

Step 2: add the right index

For a LOWER(name) LIKE 'prefix%' pattern on Postgres, a functional btree index with text_pattern_ops is the correct choice - it covers prefix matching after case folding:

CREATE INDEX idx_products_name_lower
    ON products (LOWER(name) text_pattern_ops);

ANALYZE products;   -- update planner statistics immediately

text_pattern_ops tells Postgres that the index should support LIKE/ILIKE prefix patterns, not just equality and range comparisons. Without it, the planner may not choose the index for LIKE 'widget%'.

Step 3: verify the plan changed

Run EXPLAIN ANALYZE again on the same query:

EXPLAIN ANALYZE
SELECT * FROM products WHERE LOWER(name) LIKE 'widget%';

You should now see Index Scan using idx_products_name_lower instead of Seq Scan. Query time drops from seconds to single-digit milliseconds.

When to use a trigram index instead

If you need substring matching (LIKE '%widget%' - the pattern does not anchor at the start), btree cannot help. Use a GIN trigram index instead:

CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX idx_products_name_trgm
    ON products USING GIN (name gin_trgm_ops);

Trigram indexes are larger and slower to build, but they handle arbitrary substrings. For prefix-only search, the functional btree approach above is faster and smaller.

Production notes

• Use CREATE INDEX CONCURRENTLY on live tables - it builds the index without holding a write lock, so your app keeps running.
• pg_stat_statements surfaces the slowest queries by total time across all executions - pair it with EXPLAIN ANALYZE to find the next target.
• Indexes add write overhead and storage. Index hot read paths; skip columns that are rarely filtered.