Avoiding trouble with indexes

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 created in this example is 422 MB large (only the table). On top of that, there is 214 MB taken up by the index. So, overall the size of the table with the index is larger than 600 MB.

The problem is that the index is of no use in...