Database Reference
In-Depth Information
Points to ponder
When using an index, you need to keep in mind the following things:
• It will make sense to index a table column when you have a handsome
number of rows in a table.
• When retrieving data, you need to make sure that good candidates for
an index are foreign keys and keys where
min()
and
max ()
can be used
when retrieving data. This means column selectivity is very important to
index effectively.
• Don't forget to remove unused indexes for better performance. Also, perform
REINDEX
on all indexes once a month to clean up the dead tuples.
• Use table partitioning along with an index if you have large amounts of data.
• When you are indexing columns with null values, consider using
a conditional index with
WHERE column_name IS NOT NULL
.
Summary
The major goal of this chapter was to explain the signiicance of index and
index creation. An index represents the portion of a table, so it amends the read
performance. You learned that PostgreSQL supports multiple types of single/
multicolumn index, partial index, expression index, and concurrent index. We
also learned that PostgreSQL supports the B-tree, hash, GiST, and GIN index
methods. An index adds overhead on every insert and update operation, and on
every
SELECT
query, an index speeds up the searches. We saw that index building
is a very expensive operation, and on a very huge table, it can take hours to
complete, so the concurrent index can avail in that case, and we need to be correct
about our index to avoid any index bloating.
Next on the list are triggers, rules, and views. The next chapter will highlight
their utilization, structure, and most importantly, how they differ as compared
to aggregate functions.
