پن کیا گیا ٹویٹ
> Because using an index doesn’t automatically mean the query is cheap.
> Here’s what’s really happening.
1. Low selectivity kills index benefits
> If a large percentage of rows have status = 'COMPLETED' (say 40–70%):
- The index does help find matching row IDs
- But it still has to return millions of matches
> At that point, an index scan is barely better than a full scan.
> Indexes shine when they eliminate most rows, not when they match half the table.
2. SELECT * forces heap lookups
> Your index is on status only.
> So the DB does:
- Scan the index to find matching row IDs
- For each match, fetch the full row from the table (heap)
> That means:
- Millions of random I/O reads
- Cache misses
- High latency
> The index helps filtering, but fetching the rows dominates the cost.
3. Index scan ≠ index-only scan
> EXPLAIN says Index Scan, not Index Only Scan.
> Why that matters:
- Index-only scans can avoid touching the table
- But SELECT * requires reading the heap
- So the DB can’t stay inside the index
4. The data may not be cache-resident
> If the working set doesn’t fit in memory:
> Every heap fetch hits disk
> Even SSDs will struggle at this scale
> 3–4 seconds is very believable
5. The index helps the planner, not the physics
> The planner chose the index because it’s slightly better than a full scan.
> But physics still applies: you’re reading a huge amount of data.
> What actually fixes it
- Don’t SELECT *, fetch only needed columns
- Use a covering index (include needed columns)
- Filter on more selective predicates
- Partition by status or time
- Pre-aggregate or move “COMPLETED” orders to cold storage
Takeaway:
- Indexes are about reducing work.
- If your predicate matches millions of rows, the work is still massive.
> An index can be used and the query can still be slow, and this is exactly that case.
> Index scans are fast when they return few rows.
When they return many, they can be slower than a full table scan.
English
