What is the correct way to use db_index?

Question

PostgreSQL

Greetings!
- - -
I have a model:

class Task(BaseModel):
    settings = JSONField(default=dict, blank=True)
    priority = models.IntegerField(default=0)
    status = models.CharField(default='new', max_length=50)  # статус выполнения задачи (new / in_work / success / ERROR)
    status_data = JSONField(default=dict, blank=True)
    status_date = models.DateTimeField(default=get_datetime_now)  # дата последнего обновления статуса
    is_on = models.BooleanField(default=True)

I often make queries like this:

Task.objects.filter(is_on=True, status='new').order_by('priority')

- - -
There are 7 million records in the table.
As the table grows, queries become slower.
- - -
Questions:
1) Do I understand correctly that to speed up it is necessary to add db_index=True, in the fields by which I often filter?
2) Does it make sense to add db_index to BooleanField? The is_on field in my case.
3) Does it make sense to add db_index to the field by which I sort? The priority field in my case.
4) What is the general principle of the appropriateness of adding an index to a field?
5) What other methods of acceleration are applicable in my case?
- - -
Thank you!

Answer 1

If queries on two fields at once, then it is better to make a Multicolumn Index: https://www.postgresql.org/docs/10/indexes-multico...

Answer 2

1) Yes, but maybe not just dumb indexes
2) Only if True << False or vice versa, otherwise you just won't get much gain. And then the index must be added to a less common value, i.e.
CREATE INDEX ON task(is_on) WHERE is_on = TRUE
3) No, because the scheduler will most likely choose to sort the result
4) The more unique the value by which the search is performed, the more appropriate the index
5) You need to add the index by the status field