Using WHERE and AND clauses with index in SQL

Question

I have two tables

CREATE TABLE profile (
    id BIGSERIAL PRIMARY KEY,
    nickname VARCHAR,
    registered_at TIMESTAMP
);
CREATE TABLE post (
    id BIGSERIAL PRIMARY KEY,
    owner_id BIGINT REFERENCES profile(id),
    body TEXT,
    inserted_at TIMESTAMP,
    likes_count INT
);

an index

CREATE INDEX ON post (owner_id);

and a query

SELECT * FROM post WHERE likes_count = ? AND owner_id = ?

Will the index be used in the query?

DBMS is not specified. It's a question from a real interview. But let's say postgres — Николай Беляков, May 27 '23 at 17:46
I've downvoted this question because I don't see any research effort from your side. There's tons of documentations on indices. — lemon, May 27 '23 at 18:33
Use explain (analyze, verbose, buffers) for this statement to get the query plan and how it was executed. That tells you also if the index was used or not. — Frank Heikens, May 27 '23 at 18:38

LanceB · Accepted Answer · 2023-05-27T17:54:04.323

3

Assuming this is MSSQL (looks like it), then yes.

Generally speaking, most database engines should utilize the index on owner_id to limit the rows returned for the select prior to scanning for the likes_count criteria, but that's not a guarantee as it is highly dependent on the RDS implementation.

With that being said, your best bet for questions like these is to view the execution plan on whatever RDS platform you're using--that will answer your question directly and specifically for the platform you're working with.

edited May 27 '23 at 17:54

answered May 27 '23 at 17:51

LanceB

46
3

Also depends of the distribution of owner_ids. Might still skip an index and go for seq_scan. https://stackoverflow.com/questions/5203755/why-does-postgresql-perform-sequential-scan-on-indexed-column But your right to just refer to execution plan. – Bert-Jan Stroop May 31 '23 at 12:12

Using WHERE and AND clauses with index in SQL

1 Answers1