Why Your Index Is Not Used: Reading EXPLAIN ANALYZE
You added the index and the query is still slow. Here is how to read a Postgres query plan properly and find the row estimate error causing it.
| Node | Means | Concern when |
|---|---|---|
| Seq Scan | Full table read | Large table with a selective filter |
| Index Scan | Index lookup plus heap fetch | Fine, but check loops |
| Index Only Scan | Answered from the index alone | Best case, check Heap Fetches is low |
| Bitmap Heap Scan | Collect matches then read heap in physical order | Normal for medium selectivity |
| Nested Loop | For each outer row, probe inner | Dangerous when outer rows are underestimated |
| Hash Join | Build a hash of one side | Watch for Batches greater than 1, which means disk spill |
| Merge Join | Both sides sorted | Fine if inputs are already ordered |
Related reading: The Connection String That Broke on a Special Character explores a connected angle.
Related reading: relation does not exist for a Table That Is Definitely There explores a connected angle.
Published on debugly.dev by Rohit Bhadani | Founder of Krova Cloud
