CODEWITHSUNDEEP
postgresqlindexing
Dan Untea
Dan Untea

Reputation: 11

How to create an index in postgresql on joined tables?

I'm pretty new to indexing so maybe this is a stupid question but anyway. I got this schema and my task is to get all the students that study in their hometown (above schools and students there is a cities table).

Diagram of the database

I came up with this:

select st.id, st.last_name, st.first_name, st.birth_year, st.birth_city_id
from students as st
         join school_students ss on st.id = ss.student_id
         join schools sc on sc.id = ss.school_id
where st.birth_city_id=sc.city_id;

Then for optimization I tried:

CREATE INDEX schools_id ON schools(city_id);
CREATE INDEX school_students_id ON school_students(student_id, school_id) ;  --does this helps somehow?
CREATE INDEX students_id ON students(birth_city_id);

But it does not work properly...

The query plan shows Seq scan instead of Index scan. So is there any way to do this Index across multiple tables or something similar?

Thanks in advance.

Upvotes: 0

Views: 137

Answers (1)

Schwern
Schwern

Reputation: 165207

You need to make the index on school_students, not schools.

CREATE INDEX school_students_idx ON school_students(student_id, school_id);

Your original index should have resulted in an error.

And if schools.city_id and students.birth_city_id are properly declared as foreign keys, they should already been indexed; you do not need to manually create them.

Note that just because an index exists does not mean Postgres will use it. Using an index can be slower than a sequential scan. If it thinks most of the rows will be returned, it might choose a sequential scan. Postgres can make wrong decisions if its table statistics are out of date, running analyze can help.

Upvotes: 1

Related Questions