Index is very similar to bookmarks in many ways: such as, helps people look for the subject faster, pre-organized list of subject, prevent users to read the whole subject to find what’s needed.
An index can help the database find matching rows without scanning the entire table, but the query planner may still choose a full scan when that is cheaper.
Compare with the dictionary and imagine where all the words are out of order, and there’s no index with it. You will have to read an entire book to find the word you are looking for. The “word” can be on the front page, or at the last. Index puts the data in order and aid user to find the data they want. However, there are possibilities where it is misused. The consequence of this leads to downgrade in performance which is worse than not using one.
Full Scan
Overhead on Insert, Delete, and update
pros
improves query speed
helps reducing a use of system resource
cons
Needs more storage to manage index
adds extra work to manage index
using index falsely can result in degrading performance
Good use case
Table with massive records
column that uses little to no Insert, Update, and Delete
column that uses JOIN, WHERE, or ORDER BY frequently
Checking an Index
For an orders table with id and customer_id columns, an index on customer_id may help queries that retrieve a small subset of a customer’s orders.
CREATE INDEX orders_customer_idx ON orders (customer_id);
EXPLAIN SELECT id FROM orders WHERE customer_id = 42;
Plain Text
복사
Read the plan rather than assuming the index is used. A small table or a query returning many rows may favor a sequential scan. EXPLAIN ANALYZE executes the statement, so use it deliberately, especially for writes or expensive queries.
The index type also matters. A B-tree supports sorted comparisons and ranges; a PostgreSQL hash index supports equality searches. An index is not a cache of the query’s complete result.
Reference
column that has little to no duplicate data
