How to optimize performances ?

[sorasful]

Hi there !

I'm in the situation where I have fairly simple Models, and I try to create a query but it takes around 1s to run, which I think is pretty long for the amount of data I have. Maybe I'm doing something bad.

I have an index on the scraped_date attribute, so I don't know why it's slow ...
I have around ~ 587 391 Entries, 356 Sources and 100 Playlists.

Here's the code I try to run :

async def get_entries_for_playlist(
    playlist_id: int, limit: int, offset: int
) -> List[Entry]:
    entries = (
        await Entry.objects.filter(source__playlists__id=playlist_id)
        .limit(limit)
        .offset(offset)
        .order_by(Entry.scraped_date.desc())
        .all()
    )

    return entries

And here are a simplified version of my models :

class Entry(ormar.Model):
    class Meta(BaseMeta):
        tablename = "entries"

    id: int = ormar.Integer(primary_key=True)
    title: str = ormar.String(max_length=300, index=True, nullable=False)
    source: Optional["Source"] = ormar.ForeignKey(to=Source, related_name="entries", ondelete="CASCADE")
    scraped_date: datetime = ormar.DateTime(
        timezone=True,
        default=datetime.utcnow,
        index=True,
    )
   

class Source(ormar.Model):
    class Meta(BaseMeta):
        tablename = "sources"

    class Config:
        extra = "allow"

    id: int = ormar.Integer(primary_key=True)
    title: str = ormar.String(max_length=400, index=True, nullable=True)
    playlists: Optional[List["Playlist"]] = ormar.ManyToMany(Playlist)

class Playlist(ormar.Model):
    class Meta(BaseMeta):
        tablename = "playlists"

    class Config:
        extra = "allow"

    id: int = ormar.Integer(primary_key=True)
    title: str = ormar.String(max_length=50, nullable=False, index=True)

Would you have an idea ? Or someway I could find a way to optimize this ? Maybe I'm skipping something .
Thanks for your time 🙏

[collerek]

Ok, so you return around 6000 entries per the playlist? (looking at your numbers).
Or what are the limit and offset values?

One thing you could skip is providing a return model in Fastapi cause it enforces the second round of parsing/validation of the data and you are sure you are already validated with ormar (although that breaks the API docs default behavior although that can probably be fixed on the side).