Home Querying one table that references another table's PK, is the other tables index used?

Questions

Querying one table that references another table's PK, is the other tables index used?

March 20, 2023

For example:

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    login_id VARCHAR(20) UNIQUE NOT NULL,
    login_password CHAR(60) NOT NULL,
    first_name VARCHAR(20),
    last_name VARCHAR(20),
    email varchar(100),
    last_login_at TIMESTAMP DEFAULT NOW() NOT NULL,
    join_at TIMESTAMP DEFAULT NOW() NOT NULL
);

CREATE TABLE todos (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES USERS(id) ON DELETE CASCADE,
    title VARCHAR(500),
    contents VARCHAR(1000),
    done BOOLEAN DEFAULT FALSE NOT NULL,
    create_at TIMESTAMP DEFAULT NOW() NOT NULL,
    modified_at TIMESTAMP DEFAULT NOW() NOT NULL
);

SELECT * FROM todos WHERE user_id = 1;

If I execute that query, is Postgres going to use the index on users(id)? (Data is enough to use index.)

>Solution :

If I execute that query, is Postgres going to use the index on users(id)?

No. The table users is not even involved in the query you show.

The index on the table users cannot help in any way to locate rows in the table todos. The FK reference only kicks in when inserting / updating values in todos(user_id) – or deleting / updating values in users(id).

Create an index on todos(user_id) to support your query. Would be a good design decision anyway to support the FK.