使用django-pgcrypto-fields时PostgreSQL解密查询性能过慢问题

针对你遇到的多加密字段解密查询耗时过长的问题，以下是具体优化方案：

1. 优化执行计划，避免全表解密 #

你的查询包含order by pickup_date_time和limit 10，如果pickup_date_time和is_active没有合适的索引，PostgreSQL可能会先扫描全表、过滤is_active、排序所有符合条件的行，再取前10条——这会导致所有符合条件的行都被解密，而非仅前10条。

操作：创建复合索引 #

CREATE INDEX idx_table_is_active_pickup_date ON table_name (is_active, pickup_date_time);

创建后重新执行explain analyze，确认执行计划显示Index Scan using idx_table_is_active_pickup_date on table_name，此时PostgreSQL会直接通过索引获取前10条符合条件的行，仅解密这10条的字段，大幅减少计算量。

2. 减少解密次数：合并加密字段 #

pgp_sym_decrypt是CPU密集型操作，每列解密都会产生开销。可以将同一业务组的字段合并为JSONB格式，加密整个JSONB字段，从而将多次解密减少为一次。

模型修改： #

from django_pgcrypto_fields.fields import JSONBPGPSymmetricField

class YourModel(models.Model):
    # 替换原有的6个加密字段为2个JSONB加密字段
    sender_contact = JSONBPGPSymmetricField()
    recipient_contact = JSONBPGPSymmetricField()
    is_active = models.BooleanField()
    pickup_date_time = models.DateTimeField()
    # 其他非加密字段...

查询方式： #

SELECT
    (pgp_sym_decrypt("table_name"."sender_contact", 'encryption_key')::jsonb)->>'contact_person_name' AS sender_name,
    (pgp_sym_decrypt("table_name"."sender_contact", 'encryption_key')::jsonb)->>'contact_person_email' AS sender_email,
    (pgp_sym_decrypt("table_name"."sender_contact", 'encryption_key')::jsonb)->>'contact_person_contact_no' AS sender_contact_no,
    (pgp_sym_decrypt("table_name"."recipient_contact", 'encryption_key')::jsonb)->>'contact_person_name' AS recipient_name,
    (pgp_sym_decrypt("table_name"."recipient_contact", 'encryption_key')::jsonb)->>'contact_person_email' AS recipient_email,
    (pgp_sym_decrypt("table_name"."recipient_contact", 'encryption_key')::jsonb)->>'contact_person_contact_no' AS recipient_contact_no
FROM "table_name"
WHERE "table_name"."is_active"
ORDER BY "table_name"."pickup_date_time"
LIMIT 10

这样每行仅需解密2次，而非原有的6次，直接降低60%左右的解密开销。

3. 缓存解密结果 #

如果解密后的数据更新频率低，可以将查询结果缓存到Redis或Django内置缓存中，避免重复解密计算。

Django代码示例： #

from django.core.cache import cache
from .models import YourModel

def get_top10_contact_data():
    cache_key = "top10_active_contact_data"
    cached_data = cache.get(cache_key)
    if cached_data:
        return cached_data
    
    # 仅查询前10条，django-pgcrypto-fields会自动解密字段
    queryset = YourModel.objects.filter(is_active=True).order_by('pickup_date_time')[:10]
    result = []
    for obj in queryset:
        result.append({
            "sender_name": obj.sender_contact_person_name,
            "sender_email": obj.sender_contact_person_email,
            "sender_contact_no": obj.sender_contact_person_contact_no,
            "recipient_name": obj.recipient_contact_person_name,
            "recipient_email": obj.recipient_contact_person_email,
            "recipient_contact_no": obj.recipient_contact_person_contact_no
        })
    
    # 缓存1小时，可根据数据更新频率调整
    cache.set(cache_key, result, timeout=3600)
    return result

4. 调整PostgreSQL配置提升CPU利用率 #

解密操作依赖CPU性能，可调整PostgreSQL配置优化：

• 增加shared_buffers（建议设置为系统内存的25%），提升数据缓存效率
• 调大work_mem，避免排序时使用磁盘临时文件
• 开启并行查询：调整max_parallel_workers_per_gather为2-4（根据CPU核心数），让PostgreSQL在扫描和处理时使用多核心

5. 确认索引有效性 #

注意：你为加密字段创建的索引对当前查询无帮助——当前查询的过滤条件是is_active、排序字段是pickup_date_time，加密字段仅在SELECT阶段被解密，不会用到其索引。需确保is_active和pickup_date_time的复合索引被正确使用（参考方案1）。

内容的提问来源于stack exchange，提问作者user3539990