Database Indexing – Part 1: How Indexes Help Find Data Faster
Recently, I was reading two books: Good Vibes, Good Life and The Mountain Is You . I usually forget things after some time 😅. So whenever I want to revisit something I've read, I go back to the book. But I noticed one difference between these two books. Good Vibes, Good Life has an index at the end of the book. Whenever I want to read about a particular topic, I can look at the index, find the…
The article explores the concept of database indexing, specifically how indexes in PostgreSQL help find data more efficiently. The author uses the analogy of an index in a book to illustrate the purpose of database indexes.
To demonstrate the efficiency of indexes, the author creates a large table named "employees" with three columns: id, name, and salary. By inserting 10 million records into this table, the author creates a scenario where searching for a specific record would take a considerable amount of time without an index.
When searching for a specific record using the id column without an index, PostgreSQL would scan the entire table, checking each row to find the desired data. However, when an index is created on the id column, PostgreSQL can use the index to quickly locate the required record.
The article explains that an Index Only Scan is used when PostgreSQL can retrieve the required data directly from the index without accessing the full table records. This approach is much faster than scanning the entire table, especially when dealing with large datasets.
The author also explains the output of the EXPLAIN command, which provides detailed information about how PostgreSQL executes a query. This output includes information about the execution plan, estimated costs, actual execution times, and buffer usage.
In summary, the article highlights the importance of database indexing and demonstrates how indexes in PostgreSQL can significantly improve query performance by allowing the database to quickly locate specific records, just like an index in a book helps find information without reading every page.
Written by urgent.news from Dev.to's reporting — not their text. Machine-written — may contain errors; check the original before relying on it.
