Indexes are not always a solution to the problem; they can also be the problem by themselves. The following example outlines a common pitfall. It should be avoided at all costs:
test=# CREATE TABLE t_test (id int, x text); CREATE TABLE test=# INSERT INTO t_test SELECT x, 'house' FROM generate_series(1, 10000000) AS x; INSERT 0 10000000 test=# CREATE INDEX idx_x ON t_test (x); CREATE INDEX
Before taking a look at the way the index is used, it makes sense to inspect the size of the table as well as the size of the indexes:
test=# SELECT pg_size_pretty(pg_relation_size('t_test')), pg_size_pretty(pg_relation_size('idx_x')); pg_size_pretty | pg_size_pretty ----------------+---------------- 422 MB | 214 MB (1 row)
The table ...