Django prefetch_related and select_related
Created: Aug 02, 2019 9:40 PM
Last Edited Time: Aug 03, 2019 5:11 PM
Status: Draft
Description
Boost ORM performance with prefetch_related() and select_related()
1. Preparation
Django-extension
-
Use Django-extension to show SQL and Execution time
pipenv install django-extensions --dev -
Add to INSTALL_APPS
# proj.settings.dev.py INSTALLED_APPS = INSTALLED_APPS + ['django_extensions'] -
Enter Django Shell with SQL printed
./manage.py shell_plus --print-sql --settings proj.settings.dev
Change LOGGING settings
good for server log watching, not recommended for Django interactive console.
# proj.settings.dev.py
LOGGING = {
'version': 1,
'disable_existing_loggers': False,
'handlers': {
'console': {
'class': 'logging.StreamHandler',
}
},
'loggers': {
'django.db.backends': {
'handlers': ['console'],
'propagate': True,
'level': 'DEBUG',
}
},
}
select_related
用于一对一，或者多对一（foreignkey field）总之是对一就好了，找出来加到查询结果了面成为额外字段
- 用于一对一，或者多对一（foreignkey field）总之是对一就好了，找出来加到查询结果了面成为额外字段
prefetch_related
用于多对一（foreignkey 的related 字段）或者多对多 ManyToMany，增加的属性是一个列表
Other Finding
- Model.objects.all() will SELECT all it related object as well, lead to a multiple SELECT
- So we should use Model.objects.values('', '',...) to skip the foreignkey field
