How to create index for join tables

Question

I am new to indexing and running into an issue where Postgresql keeps saying there's an error with my code.

Do I need to add the foreign key on the bookings table that references the primary key of the members table.

How can I implement indexing if my SQL table structure is as follows and I have a JOIN query with WHERE clause using joindate

SELECT *
FROM bookings b
JOIN members m ON m.memid = b.memid WHERE joindate > '2012-08-01';

bookings                                   members
---------                                 ----------
PK: bookid                                 PK: memid
facid                                      surname
memid                                      firstname
starttime                                  address
slots                                      joindate
                                           recommendedby

My attempted query

CREATE JOIN INDEX join_memid_index FOR members JOIN bookings ON  bookings.memid = members.memid

This is the error I got when I run above query

ERROR: syntax error at or near "JOIN"
LINE 1: CREATE JOIN INDEX join_memid_index FOR members JOIN bookings...
^
SQL state: 42601
Character: 8

Index not needed for joining (it is used for performance increase). Postgresql keeps saying there's an error with my code Provide complete and unchanged query text and complete unchanged error message. — Akina
If you're working with postgresql, why have you tagged the question mysql and sql-server? Those are different database products — Damien_The_Unbeliever
where did you get 'CREATE JOIN INDEX' -join in an index create is not mentioned in postgresql documentation postgresql.org/docs/9.1/sql-createindex.html — P.Salmon

Akina Akina · Accepted Answer · 2020-11-13T07:58:39

Index is a structure local to a table. It only improves the query, not performs the action.

You must index each table separately:

CREATE INDEX idx_members_memid ON members (memid);
CREATE INDEX idx_bookings_memid ON bookings (memid);

The server will use this index for a more efficient join process if it decides that this will improve performance. If it doesn't decide to do this, it will ignore the presence of the index and use a table scan.

By the way, the server may make an erroneous decision...

Maybe the composite index on members which includes joindate column will be more effective than single index by memid:

CREATE INDEX idx_members_memid_joindate_1 ON members (memid, joindate);
CREATE INDEX idx_members_memid_joindate_1 ON members (joindate, memid);

Only execution plan investigation will show what of above 3 indices by members (or none) will be used by server. And its decision may change depends on data statistic (rows amount, both total and matched the condition) and parameter value.

How to create index for join tables

1 Answers