python - 如何优化 django 查询中的注释？

Question

这段代码需要 55 秒才能运行，有什么办法可以优化吗？这是不可行的

job_list = Job.objects.filter(Q(hiring_manager=emp) | Q( followers=emp) | Q(conductors=emp)).values('pk')
_candidates = Candidate.objects.filter( job__in=job_list, apply_date__range=[date_initial, date_end] ).order_by('-apply_date')
start_time2 = tm.time()
_candidates_ext = _candidates.annotate(month=TruncMonth('apply_date')).values('month').annotate(jcount=Count('pk')).order_by('month')
context['candidates_total'] = len(_candidates)
#context['candidates_total'] = _candidates.count()
context['candidates_ext'] = _candidates_ext
print("--- %s seconds ---" % (tm.time() - start_time2))
print("===== Connections =====")
print(len(connection.queries))
import pdb; pdb.set_trace()

我是公司的新手，希望能有好的开始

使用 python 2.7 和 DJango 1.8

编辑 我在应用日期中添加了 db_index=True 和这个帮助我，这条线需要 0.04 秒才能运行：

 _candidates_ext = _candidates.annotate(month=TruncMonth('apply_date')).values('month').annotate(jcount=Count('pk')).order_by('month')

但是这些作业大约需要 35 秒

这是我的打印（connection.queries）

[{u'time': u'0.001', u'sql': u'SELECT @@SQL_AUTO_IS_NULL'}, {u'time': u'0.000', u'sql': u'SELECT VERSION()'}, {u'time': u'0.000', u'sql': u"SELECT `django_session`.`session_key`, `django_session`.`session_data`, `django_session`.`expire_date` FROM `django_session` WHERE (`django_session`.`session_key` = 'd8lx1t4cpa9u9ln2v584skbqh4lypv4y' AND `django_session`.`expire_date` > '2020-01-08 12:20:14.872897')"}, {u'time': u'0.000', u'sql': u'SELECT `auth_user`.`id`, `auth_user`.`password`, `auth_user`.`last_login`, `auth_user`.`is_superuser`, `auth_user`.`username`, `auth_user`.`first_name`, `auth_user`.`last_name`, `auth_user`.`email`, `auth_user`.`is_staff`, `auth_user`.`is_active`, `auth_user`.`date_joined` FROM `auth_user` WHERE `auth_user`.`id` = 9482'}, {u'time': u'0.000', u'sql': u'SELECT `company_company`.`owner_id`, `company_company`.`name`, `company_company`.`cnpj`, `company_company`.`phone`, `company_company`.`website`, `company_company`.`zipcode`, `company_company`.`address`, `company_company`.`adddressnumber`, `company_company`.`adddressnumbercomp`, `company_company`.`neighbourhood`, `company_company`.`state`, `company_company`.`city`, `company_company`.`country`, `company_company`.`size`, `company_company`.`photo`, `company_company`.`date`, `company_company`.`max_users`, `company_company`.`active`, `company_company`.`auto_bill`, `company_company`.`max_cv_import_by_day`, `company_company`.`company_language` FROM `company_company` WHERE `company_company`.`owner_id` = 5705'}, {u'time': u'0.000', u'sql': u'SELECT `company_employee`.`user_id`, `company_employee`.`firm_id`, `company_employee`.`photo`, `company_employee`.`department` FROM `company_employee` WHERE `company_employee`.`user_id` = 9482'}, {u'time': u'0.000', u'sql': u'SELECT `company_company`.`owner_id`, `company_company`.`name`, `company_company`.`cnpj`, `company_company`.`phone`, `company_company`.`website`, `company_company`.`zipcode`, `company_company`.`address`, `company_company`.`adddressnumber`, `company_company`.`adddressnumbercomp`, `company_company`.`neighbourhood`, `company_company`.`state`, `company_company`.`city`, `company_company`.`country`, `company_company`.`size`, `company_company`.`photo`, `company_company`.`date`, `company_company`.`max_users`, `company_company`.`active`, `company_company`.`auto_bill`, `company_company`.`max_cv_import_by_day`, `company_company`.`company_language` FROM `company_company` WHERE `company_company`.`owner_id` = 5705'}, {u'time': u'0.000', u'sql': u'SELECT `company_company`.`owner_id`, `company_company`.`name`, `company_company`.`cnpj`, `company_company`.`phone`, `company_company`.`website`, `company_company`.`zipcode`, `company_company`.`address`, `company_company`.`adddressnumber`, `company_company`.`adddressnumbercomp`, `company_company`.`neighbourhood`, `company_company`.`state`, `company_company`.`city`, `company_company`.`country`, `company_company`.`size`, `company_company`.`photo`, `company_company`.`date`, `company_company`.`max_users`, `company_company`.`active`, `company_company`.`auto_bill`, `company_company`.`max_cv_import_by_day`, `company_company`.`company_language` FROM `company_company` WHERE `company_company`.`owner_id` = 5705'}, {u'time': u'0.000', u'sql': u'SELECT `auth_group`.`id`, `auth_group`.`name` FROM `auth_group` INNER JOIN `auth_user_groups` ON (`auth_group`.`id` = `auth_user_groups`.`group_id`) WHERE `auth_user_groups`.`user_id` = 9482 LIMIT 1'}, {u'time': u'0.546', u'sql': u"SELECT COUNT(*) AS `__count` FROM `combo_job` LEFT OUTER JOIN `combo_job_followers` ON (`combo_job`.`id` = `combo_job_followers`.`job_id`) LEFT OUTER JOIN `combo_job_conductors` ON (`combo_job`.`id` = `combo_job_conductors`.`job_id`) WHERE (`combo_job`.`date_stopped` BETWEEN '2019-01-01 02:00:00' AND '2019-02-01 02:00:00' AND `combo_job`.`sla_stopped` = 1 AND (`combo_job`.`hiring_manager_id` = (SELECT U0.`user_id` AS Col1 FROM `company_employee` U0 WHERE (U0.`firm_id` = 5705 AND U0.`user_id` = 9482)) OR `combo_job_followers`.`user_id` = (SELECT U0.`user_id` AS Col1 FROM `company_employee` U0 WHERE (U0.`firm_id` = 5705 AND U0.`user_id` = 9482)) OR `combo_job_conductors`.`user_id` = (SELECT U0.`user_id` AS Col1 FROM `company_employee` U0 WHERE (U0.`firm_id` = 5705 AND U0.`user_id` = 9482))))"}, {u'time': u'1.290', u'sql': u"SELECT COUNT(*) AS `__count` FROM `combo_job` LEFT OUTER JOIN `combo_job_followers` ON (`combo_job`.`id` = `combo_job_followers`.`job_id`) LEFT OUTER JOIN `combo_job_conductors` ON (`combo_job`.`id` = `combo_job_conductors`.`job_id`) WHERE (`combo_job`.`pub_date` BETWEEN '2019-01-01' AND '2019-02-01' AND (`combo_job`.`hiring_manager_id` = (SELECT U0.`user_id` AS Col1 FROM `company_employee` U0 WHERE (U0.`firm_id` = 5705 AND U0.`user_id` = 9482)) OR `combo_job_followers`.`user_id` = (SELECT U0.`user_id` AS Col1 FROM `company_employee` U0 WHERE (U0.`firm_id` = 5705 AND U0.`user_id` = 9482)) OR `combo_job_conductors`.`user_id` = (SELECT U0.`user_id` AS Col1 FROM `company_employee` U0 WHERE (U0.`firm_id` = 5705 AND U0.`user_id` = 9482))))"}, {u'time': u'0.569', u'sql': u"SELECT COUNT(*) AS `__count` FROM `combo_job` LEFT OUTER JOIN `combo_job_followers` ON (`combo_job`.`id` = `combo_job_followers`.`job_id`) LEFT OUTER JOIN `combo_job_conductors` ON (`combo_job`.`id` = `combo_job_conductors`.`job_id`) WHERE (`combo_job`.`date_stopped` BETWEEN '2019-01-01 02:00:00' AND '2019-02-01 02:00:00' AND `combo_job`.`sla_stopped` = 1 AND (`combo_job`.`hiring_manager_id` = (SELECT U0.`user_id` AS Col1 FROM `company_employee` U0 WHERE (U0.`firm_id` = 5705 AND U0.`user_id` = 9482)) OR `combo_job_followers`.`user_id` = (SELECT U0.`user_id` AS Col1 FROM `company_employee` U0 WHERE (U0.`firm_id` = 5705 AND U0.`user_id` = 9482)) OR `combo_job_conductors`.`user_id` = (SELECT U0.`user_id` AS Col1 FROM `company_employee` U0 WHERE (U0.`firm_id` = 5705 AND U0.`user_id` = 9482))))"}, {u'time': u'0.202', u'sql': u"SELECT COUNT(*) AS `__count` FROM `combo_companycandidate` WHERE (`combo_companycandidate`.`company_id` = 5705 AND `combo_companycandidate`.`date` BETWEEN '2019-01-01 02:00:00' AND '2019-02-01 02:00:00')"}, {u'time': u'49.747', u'sql': u"SELECT COUNT(*) AS `__count` FROM `combo_candidate` WHERE (`combo_candidate`.`job_id` IN (SELECT V0.`id` AS Col1 FROM `combo_job` V0 LEFT OUTER JOIN `combo_job_followers` V2 ON (V0.`id` = V2.`job_id`) LEFT OUTER JOIN `combo_job_conductors` V4 ON (V0.`id` = V4.`job_id`) WHERE (V0.`hiring_manager_id` = (SELECT U0.`user_id` AS Col1 FROM `company_employee` U0 WHERE (U0.`firm_id` = 5705 AND U0.`user_id` = 9482)) OR V2.`user_id` = (SELECT U0.`user_id` AS Col1 FROM `company_employee` U0 WHERE (U0.`firm_id` = 5705 AND U0.`user_id` = 9482)) OR V4.`user_id` = (SELECT U0.`user_id` AS Col1 FROM `company_employee` U0 WHERE (U0.`firm_id` = 5705 AND U0.`user_id` = 9482)))) AND `combo_candidate`.`apply_date` BETWEEN '2019-01-01 02:00:00' AND '2019-02-01 02:00:00')"}]

我在我的终端收到这个：

/home/jobconvo/.virtualenvs/combo/local/lib/python2.7/site-packages/django/db/models/fields/__init__.py:1393: RuntimeWarning: DateTimeField Candidate.status_date received a naive datetime (2019-02-01 00:00:00) while time zone support is active.
  RuntimeWarning)

Edit2：此 Sql 执行：

SQL Executed
SELECT ••• FROM `combo_candidate` WHERE (`combo_candidate`.`job_id` IN (SELECT ••• FROM `combo_job` V0 LEFT OUTER JOIN `combo_job_followers` V2 ON (V0.`id` = V2.`job_id`) LEFT OUTER JOIN `combo_job_conductors` V4 ON (V0.`id` = V4.`job_id`) WHERE (V0.`hiring_manager_id` = (SELECT ••• FROM `company_employee` U0 WHERE (U0.`firm_id` = 5705 AND U0.`user_id` = 9482)) OR V2.`user_id` = (SELECT ••• FROM `company_employee` U0 WHERE (U0.`firm_id` = 5705 AND U0.`user_id` = 9482)) OR V4.`user_id` = (SELECT ••• FROM `company_employee` U0 WHERE (U0.`firm_id` = 5705 AND U0.`user_id` = 9482)))) AND `combo_candidate`.`apply_date` BETWEEN '2019-01-01 02:00:00' AND '2019-02-01 02:00:00')
Time
45410,722971 ms

标签： pythondjangodjango-annotate