Mastering PostgreSQL 9.6

Using indexes in an intelligent way

So far, applying an index feels like the Holy Grail, which always improves performance magically. However, this is not the case. Indexes can also be pretty pointless in some cases.

Before digging into things more deeply, here is the data structure we are using for this example. Remember that there are only two distinct names and unique IDs:

test=# \d t_test                 Table "public.t_test"  Column |  Type   |  Modifiers                       --------+---------+------------------------------------  id     | integer | not null default nextval('t_test_id_seq'::regclass)  name   | text    |  Indexes:     "idx_id" btree (id)

At this point, one index has been defined, which covers the id column. In the next step, the name column will be queried. Before doing ...