Misplaced or missing indexes

Missing indexes on column expressions causes a full table scan. There are several cases where indexes can help to increase performance. For example, it is a good practice to index foreign keys. To test this case, let's create a table and populate it as follows:

CREATE TABLE success_story (id int, description text, guru_id int references guru(id));INSERT INTO success_story (id, description, guru_id) SELECT n, md5(n::text), random()*99999+1 from generate_series(1,200000) as foo(n) ;

Now, to get a certain guru success_story, one can write a simple query that joins both guru and success_story tables, as follows:

postgres=# EXPLAIN ANALYZE SELECT * FROM guru inner JOIN success_story on guru.id = success_story.guru_id ...

Get Learning PostgreSQL 10 - Second Edition now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.