Postgres - understanding indexes

Question

Using Postgres SQL - I have general question regarding indexing to improve efficiencies.

What are the best indexes that should be created for the next query?

SELECT task_name, user_name
FROM tasks
WHERE (user_id = 1 OR task_type = 'SOME_TYPE') and is_deleted = FALSE
GROUP BY task_name, user_name

• Does the columns in WHERE clause (user_id, task_type, is_deleted) need to be in the same index as in the GROUP BY (task_name, user_name)?

• Does the OR operator means that the columns need to be on different indexes?

• From what I'm familiar with, indexing boolean columns is usually not a good idea, since the downsides are bigger than the benefits. Does the boolean column 'is_deleted' needs to be in the index with the columns in the WHERE clause?

Thanks a lot for helping me understand it.

Answer 1

It depends on the value distribution and the table size what the best indexes are for this situation.

Assuming that the table is big enough and the conditions are all selective, I'd use:

CREATE INDEX tasks_user_id_idx   ON tasks(user_id)   WHERE NOT is_deleted;
CREATE INDEX tasks_task_type_idx ON tasks(task_type) WHERE NOT is_deleted;

You can either index for the WHERE clause or for the GROUP BY clause, and I chose the former option. Try if a multi-column index on the GROUP BY columns works better for you.

It does not make much sense to index a boolean column, a partial index like in my example is usually better.

A multi-column index doesn't help you with an OR condition, your only hope here is a bitmap index scan.