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's 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, you can write a 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 WHERE ...

Get Learning PostgreSQL 11 now with O’Reilly online learning.

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