Database Indexing Pitfalls
Adding indexes to your database is the most effective way to speed up slow queries. However, applying the wrong index type is a common mistake that can actually slow down writes while doing nothing to optimize reads.
Case Study: E-Commerce Filter Starvation
An e-commerce client had a product search page allowing users to filter by dynamic tags (stored as a PostgreSQL array). As the product catalog reached 1 million items, filters took 4-5 seconds to respond. The database CPU was consistently pegged at 100%.
The Bug: B-Tree Index on Array Columns
We inspected the table structure and found that the tag column had a standard B-Tree index:
CREATE INDEX idx_products_tags ON products(tags);When executing filters like SELECT * FROM products WHERE 'electronics' = ANY(tags);, PostgreSQL ignored the B-Tree index. This is because B-Tree indexes are designed for scalar value comparisons (<, =, >) and cannot efficiently parse individual values inside array or JSONB structures.
The Fix: Implementing GIN Indexes
We dropped the B-Tree index and created a Generalized Inverted Index (GIN). GIN is specifically built to map composite items to their container rows:
DROP INDEX idx_products_tags;
CREATE INDEX idx_products_tags_gin ON products USING gin(tags);We also verified that queries were formatted to use index-friendly operators like containment (@>):
SELECT * FROM products WHERE tags @> ARRAY['electronics'];This change reduced the query execution time from 4.5 seconds to 12 milliseconds, returning the database CPU load to less than 15%.