Low performance on select_all() call
Igralino opened this issue · 1 comments
Igralino commented
Describe the bug
I have a query in my code that gets one user from the table with all related fields. It looks like that:
user = await UserModel.objects.select_all().get(id=user_id)
This request provides following SQL query: (I uncommented line №295 in queryset.py
to get raw query)
Long SQL statement
SELECT
"user".id AS id,
"user".created_at AS created_at,
"user".updated_at AS updated_at,
"user".name AS name,
"user".surname AS surname,
"user".photo_url AS photo_url,
"user".email AS email,
"user".phone_number AS phone_number,
"user".notifications_enabled AS notifications_enabled,
bu1a62_event_user.id AS bu1a62_id,
bu1a62_event_user.eventmodel AS bu1a62_eventmodel,
bu1a62_event_user.usermodel AS bu1a62_usermodel,
fz662c_event.id AS fz662c_id,
fz662c_event.created_at AS fz662c_created_at,
fz662c_event.updated_at AS fz662c_updated_at,
fz662c_event.creator AS fz662c_creator,
fz662c_event.name AS fz662c_name,
fz662c_event.description AS fz662c_description,
fz662c_event.chat_url AS fz662c_chat_url,
fz662c_event.sport AS fz662c_sport,
fz662c_event.start_time AS fz662c_start_time,
fz662c_event.location AS fz662c_location,
fz662c_event.max_participants AS fz662c_max_participants,
djb468_event.id AS djb468_id,
djb468_event.created_at AS djb468_created_at,
djb468_event.updated_at AS djb468_updated_at,
djb468_event.creator AS djb468_creator,
djb468_event.name AS djb468_name,
djb468_event.description AS djb468_description,
djb468_event.chat_url AS djb468_chat_url,
djb468_event.sport AS djb468_sport,
djb468_event.start_time AS djb468_start_time,
djb468_event.location AS djb468_location,
djb468_event.max_participants AS djb468_max_participants,
bqfbf8_user_sport.id AS bqfbf8_id,
bqfbf8_user_sport.sportmodel AS bqfbf8_sportmodel,
bqfbf8_user_sport.usermodel AS bqfbf8_usermodel,
vwcd96_sport.id AS vwcd96_id,
vwcd96_sport.created_at AS vwcd96_created_at,
vwcd96_sport.updated_at AS vwcd96_updated_at,
vwcd96_sport.name AS vwcd96_name,
vwcd96_sport.emoji AS vwcd96_emoji,
ugc52b_firebase_token.id AS ugc52b_id,
ugc52b_firebase_token.created_at AS ugc52b_created_at,
ugc52b_firebase_token.updated_at AS ugc52b_updated_at,
ugc52b_firebase_token."user" AS ugc52b_user,
ugc52b_firebase_token.device_id AS ugc52b_device_id,
ugc52b_firebase_token.token AS ugc52b_token,
ugc52b_firebase_token.ios_app_version AS ugc52b_ios_app_version
FROM
"user"
LEFT OUTER JOIN event_user AS bu1a62_event_user ON bu1a62_event_user.usermodel = "user".id
LEFT OUTER JOIN event AS fz662c_event ON fz662c_event.id = bu1a62_event_user.eventmodel
LEFT OUTER JOIN event AS djb468_event ON djb468_event.creator = "user".id
LEFT OUTER JOIN user_sport AS bqfbf8_user_sport ON bqfbf8_user_sport.usermodel = "user".id
LEFT OUTER JOIN sport AS vwcd96_sport ON vwcd96_sport.id = bqfbf8_user_sport.sportmodel
LEFT OUTER JOIN firebase_token AS ugc52b_firebase_token ON ugc52b_firebase_token.user = "user".id
WHERE
"user".id = 'auth0|65215cfdcc2577c97118e6a1'
ORDER BY
"user".id,
fz662c_event.id,
djb468_event.id,
vwcd96_sport.id,
ugc52b_firebase_token.id
When I execute this statement directly via SQL - it takes about 300ms, but when I call the function - it takes about 3-4 seconds to get the result.
I have only 24 rows in events
table, 18 in event_user
, 8 in sports
and 7 in users
.
UserModel.py
class UserSport(ormar.Model):
class Meta(BaseMeta):
tablename = "user_sport"
constraints = [ormar.UniqueColumns("sportmodel", "usermodel")]
id: int = ormar.Integer(primary_key=True)
sportmodel: int = ormar.Integer()
usermodel: str = ormar.String(nullable=False, min_length=1, max_length=200)
class UserModel(ormar.Model):
id: str = ormar.String(primary_key=True, min_length=1, max_length=200)
favourite_sports: List[SportModel] | None = ormar.ManyToMany(SportModel, through=UserSport)
# other fields
EventModel.py
class EventUser(ormar.Model):
class Meta(BaseMeta):
tablename = "event_user"
constraints = [ormar.UniqueColumns("eventmodel", "usermodel")]
id: int = ormar.Integer(primary_key=True)
eventmodel: int = ormar.Integer()
usermodel: str = ormar.String(nullable=False, min_length=1, max_length=200)
class EventModel(ormar.Model):
id: int = ormar.Integer(primary_key=True)
creator: UserModel = ormar.ForeignKey(UserModel, related_name="created_events")
sport: SportModel = ormar.ForeignKey(SportModel)
users: List[UserModel] = ormar.ManyToMany(UserModel, unique=True, related_name="attending_events", through=EventUser)
# other fields
SportModel.py
class SportModel(ormar.Model):
id: int = ormar.Integer(primary_key=True)
name: str = ormar.String(max_length=50, min_length=1)
# other fields
To Reproduce
- Create several models with ForeignField and ManyToMany relationships
- Add some data to database
- Call
get
request withselect_all()
- Wait for a long time to get result
Expected behavior
Request will take much less time than now
Versions (please complete the following information):
- Database: Postresql
- python = "3.11.5"
- fastapi = "0.103"
- ormar = "0.12.2"
- pydantic = "1.10.8"
collerek commented
Can you check again with ormar 0.20.0 with pydantic v2 support?