How to use database index correctly when performing search on multiple fields?

Suppose we have the following model (i'm making a Django example, but i suppose my question extrapolates on any framework):

from django.db import models

class Card(models.Model):
    title = models.CharField(max_length=255)
    description = models.CharField(max_length=1024)

    tags = models.ManyToManyField(
        Tag,
        related_name="cards",
    )

    class Meta:
        indexes = [
            ["title", "description"],
        ]

class Tag(models.Model):
    uuid = models.UUIDField(default=uuid4, primary_key=True)
    title = models.CharField(max_length=255, unique=True)

I'm later using those two fields in the custom search later:

from django.db.models import Q

def custom_filter_queryset(queryset, text):
    return queryset.filter(
        Q(title__contains=text) | Q(description__contains=text)
    ).distinct()

custom_filter_queryset(Card.objects.all().prefetch_related("tags"), "...")

Questions:

• am i correcly taking advantage of a built index on those fields?
• can i somehow create an index to optimize many-to-many fetching?

UPD: i'm using Postgres, but i suppose this extrapolates on any relational SQL DB

1. Not exactly.

The order of the indicated fields is important, so in that case queries like .filter(title=..., description=...) will benefit from the index, but .filter(description=...) will not. If your queries will include both title OR desc and title AND desc, use three indexes:

    ...
    class Meta:
        indexes = [
            ["title", "description"],
            ["title"],
            ["description"],
        ]

2. Note that prefetch_related does evaluate the queryset, so subsequent filters will be executed in Python.

Use special Prefetch object to execute filters on DB side:

from django.db.models import Prefetch

Card.objects.prefetch_related(Prefetch('tags'))

Ref: https://docs.djangoproject.com/en/4.1/ref/models/querysets/