PostgreSQL: Index

Hey everyone, in this post we’ll see a very simple example of how indexes can drastically improve read speeds.

We will take a simple enough scenario where in we have 2 tables, student and school.

CREATE TABLE public.school (
id serial,
PRIMARY KEY (id),
name varchar(255)
);

CREATE TABLE public.student (
id serial,
name varchar(255),
school_id int,
PRIMARY KEY (id),
FOREIGN KEY (school_id) REFERENCES  school(id)
);

Let’s load some random data in these tables now:

INSERT INTO school 
(id, name) 
VALUES 
(generate_series(1, 150000), 'default');

INSERT INTO student 
(id, school_id, name) 
VALUES 
(generate_series(1, 15000000), floor(random() * 150000) + 1, 'default')

As of now, we have created no indexes, let’s see how the select query performs:

explain analyze select * from student where school_id = 3567;

Now, let’s add index and see how it impacts query time

CREATE INDEX student_school_id ON student (school_id);

Curious to see more:

Head to https://github.com/singhalkarun/postgresql-benchmarks/