django - Django + PostrgreSQL 性能问题

Question

您好）使用 django-pgcrypto-fields 后出现性能问题：

型号是：

class AssignedEmployee(models.Model):
    user_email = CharPGPSymmetricKeyField(max_length=50)
    project = models.ForeignKey('Project', on_delete=models.CASCADE, db_index=True)
    status_of_assignment = models.BooleanField(default=False, null=True)
    datetime_of_assignment = DateTimePGPSymmetricKeyField(null=True)
    exception_text = CharPGPSymmetricKeyField(max_length=255, null=True)

    class Meta:
        unique_together = (('user_email', 'project'), )

意见.py：第一个：

def action_required_projects(request):
    assigned_projects = AssignedEmployee.objects.filter(
        user_email__iexact=request.user.email,
        status_of_assignment=False).select_related('project').order_by('project_id')

第二：

def signed_projects(request):
    assigned_projects = AssignedEmployee.objects.filter(
        user_email__iexact=request.user.email,
        status_of_assignment=True).select_related('project').order_by('-project_id')

所以第二个视图的工作速度要慢 18 倍。如果我将 status_of_assignment=True 更改为 status_of_assignment=False 它的工作速度与第一个一样快。explain() 和 print_sql 向我展示了这一点：对于第一个视图：

    SELECT "assignedemployee"."id",
       pgp_sym_decrypt("assignedemployee"."user_email", 'secret_key')::TEXT,
       "assignedemployee"."project_id",
       "assignedemployee"."status_of_assignment",
       pgp_sym_decrypt("assignedemployee"."datetime_of_assignment", 'secret_key')::TIMESTAMP,
       "project"."id",
       pgp_sym_decrypt("project"."language", 'secret_key')::TEXT,
       pgp_sym_decrypt("project"."client_name", 'secret_key')::TEXT,
       pgp_sym_decrypt("project"."engagement_name", 'secret_key')::TEXT,
       pgp_sym_decrypt("project"."deadline", 'secret_key')::DATE,
       "project"."status",
       pgp_sym_decrypt("project"."access", 'secret_key')::TEXT
FROM "assignedemployee"
INNER JOIN "project" ON ("assignedemployee"."project_id" = "project"."id")
WHERE ("assignedemployee"."status_of_assignment" = FALSE
       AND UPPER(pgp_sym_decrypt("assignedemployee"."user_email", 'secret_key')::TEXT::text) = UPPER([e-mail]))
ORDER BY "assignedemployee"."project_id" ASC
Sort  (cost=238.09..238.10 rows=3 width=189) (actual time=226.059..226.059 rows=3 loops=1)
  Sort Key: assignedemployee.project_id
  Sort Method: quicksort  Memory: 25kB
  ->  Nested Loop  (cost=0.57..238.06 rows=3 width=189) (actual time=218.194..226.005 rows=3 loops=1)
        ->  Index Scan using assign_status__63421e_idx on assignedemployee  (cost=0.29..213.08 rows=3 width=196) (actual time=215.875..219.181 rows=3 loops=1)
              Index Cond: (status_of_assignment = false)
              Filter: ((NOT status_of_assignment) AND (upper(pgp_sym_decrypt(user_email, 'secret_key'::text)) = '[e-mail]'::text))
              Rows Removed by Filter: 536
        ->  Index Scan using projec_id_dcb74d_idx on project  (cost=0.28..8.30 rows=1 width=443) (actual time=0.010..0.010 rows=1 loops=3)
              Index Cond: (id = assignedemployee.project_id)
Planning time: 1.842 ms
Execution time: 226.100 ms

第二次

SELECT "assignedemployee"."id",
       pgp_sym_decrypt("assignedemployee"."user_email", 'secret_key')::TEXT,
       "assignedemployee"."project_id",
       "assignedemployee"."status_of_assignment",
       pgp_sym_decrypt("assignedemployee"."datetime_of_assignment", 'secret_key')::TIMESTAMP,
       "project"."id",
       pgp_sym_decrypt("project"."language", 'secret_key')::TEXT,
       pgp_sym_decrypt("project"."client_name", 'secret_key')::TEXT,
       pgp_sym_decrypt("project"."engagement_name", 'secret_key')::TEXT,
       pgp_sym_decrypt("project"."deadline", 'secret_key')::DATE,
       "project"."status",
       pgp_sym_decrypt("project"."access", 'secret_key')::TEXT
FROM "assignedemployee"
INNER JOIN "project" ON ("assignedemployee"."project_id" = "project"."id")
WHERE ("assignedemployee"."status_of_assignment" = TRUE
       AND UPPER(pgp_sym_decrypt("assignedemployee"."user_email", 'secret_key')::TEXT::text) = UPPER([e-mail]))
ORDER BY "assignedemployee"."project_id" DESC
Sort  (cost=1988.54..1988.95 rows=166 width=189) (actual time=12694.159..12694.160 rows=8 loops=1)
  Sort Key: assignedemployee.project_id DESC
  Sort Method: quicksort  Memory: 26kB
  ->  Hash Join  (cost=427.77..1982.42 rows=166 width=189) (actual time=8625.176..12694.084 rows=8 loops=1)
        Hash Cond: (assignedemployee.project_id = project.id)
        ->  Seq Scan on assignedemployee  (cost=0.00..1549.64 rows=166 width=196) (actual time=8613.148..12665.380 rows=8 loops=1)
              Filter: (status_of_assignment AND (upper(pgp_sym_decrypt(user_email, 'secret_key'::text)) = '[e-mail]'::text))
              Rows Removed by Filter: 33800
        ->  Hash  (cost=397.90..397.90 rows=2390 width=443) (actual time=9.573..9.573 rows=2390 loops=1)
              Buckets: 4096  Batches: 1  Memory Usage: 1150kB
              ->  Seq Scan on project  (cost=0.00..397.90 rows=2390 width=443) (actual time=0.026..8.223 rows=2390 loops=1)
Planning time: 4.898 ms
Execution time: 12698.741 ms

所以我决定设置索引：

class Meta:
unique_together = (('user_email', 'project'), )
indexes = (models.Index(fields=['-status_of_assignment']),
           models.Index(fields=['status_of_assignment']),
           models.Index(fields=['user_email', '-status_of_assignment']),
           models.Index(fields=['project']),
           models.Index(fields=['-project']),
           models.Index(fields=['user_email', '-status_of_assignment', '-project']),
           models.Index(fields=['user_email', '-status_of_assignment', 'project']))
index_together = [
    ['user_email', 'status_of_assignment'],
]

我可以在 dbeaver 中看到它们，但性能和解释（）中没有任何变化。

我需要如何设置索引，或者问题可能出在其他地方？

标签： djangopostgresqlindexingpgcrypto