How to create an index in postgresql on joined tables?

Question

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).

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.

Presumably that 2nd CREATE INDEX is actually on school_students? — AdamKG
– AdamKG, Commented Oct 8, 2020 at 20:33

Schwern · Accepted Answer · 2020-10-08 22:10:48Z

1

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.

answered Oct 8, 2020 at 22:10

Schwern

167k28 gold badges226 silver badges371 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to create an index in postgresql on joined tables?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related