Is it worth Indexing day_of_week column?

Question

I have a users table that has a day_of_week column, it is an integer(0...6). Every day I have to run a script and iterate over the users of that day.

So, should I mark this column with an index for faster perfomance? The query will always return 1/7 of the total, I'm afraid it may occur the opposite effect if I add an index. Imagine a 10k-100k rows in the users table.

By the way I'm using PostgreSQL if it matters.

Answer 1

No. Don't index on that as its cardinality is very low so a direct table scan is cheaper. It could only be worth if the result set should be sorted on it. But that would depend on other factors.

When a value is present in more than a few percents of the total rows of the table then a query filtering on it will have to visit most of the table's pages after finding them in the index. So there is no point in searching the index first as that is just double work.