Django+PostgreSQL下替代distinct的高效查询方案

Hey there! Let's tackle this slow distinct query issue you're facing—1 million rows is a significant dataset, so optimizing this makes total sense. Your current query is hitting performance bottlenecks because distinct('field1','field2') (which translates to PostgreSQL's DISTINCT ON) can be expensive without proper indexing or query structure adjustments. Here are practical, efficient alternatives and fixes:

1. Add Targeted Database Indexes (First Priority!) #

The biggest culprit for slow queries on large datasets is usually missing indexes. For your query, create these indexes to let PostgreSQL avoid full-table scans:

• Index for the filter condition: Add a B-tree index on field since you're doing an exact match:

  class YourModel(models.Model):
      # ... your existing fields ...
      class Meta:
          indexes = [
              models.Index(fields=['field']),
          ]
  

• Index for the exclude condition: Add an index on tr to speed up the NOT IN check:

  models.Index(fields=['tr']),
  

• Composite index for distinct fields: Create a composite index on field1 and field2—this lets PostgreSQL pull the distinct values directly from the index without hitting the main table:

  models.Index(fields=['field1', 'field2']),
  

After adding these, run migrations and re-test your query—this alone could cut down execution time drastically.

2. Use values() + distinct() Instead of distinct(*fields) #

Your current distinct('field1','field2') returns full model instances, which means PostgreSQL has to fetch all columns for matching rows before deduplicating. If you only need the values of field1 and field2, switch to this approach:

unique_pairs = YourModel.objects.filter(field=str(field))\
                               .exclude(tr__in=['some','some1'])\
                               .values('field1', 'field2')\
                               .distinct()

This generates a SELECT DISTINCT field1, field2 FROM ... query, which only fetches the two columns you care about. The result is a list of dictionaries with field1 and field2 values—much lighter and faster than fetching full instances.

3. Replace DISTINCT with GROUP BY #

In PostgreSQL, GROUP BY field1, field2 often performs similarly to DISTINCT but can sometimes leverage indexes more effectively. In Django, you can achieve this by combining values() with annotate() (even if you don't need an aggregation):

unique_pairs = YourModel.objects.filter(field=str(field))\
                               .exclude(tr__in=['some','some1'])\
                               .values('field1', 'field2')\
                               .annotate()  # Triggers GROUP BY on the values fields

The annotate() without any arguments tells Django to group by the fields in values(), giving you the same deduplicated result set as distinct().

4. Cache Deduplicated Results #

If the values of field1 and field2 don't change frequently, cache the result set to avoid hitting the database on every request. Use Django's built-in cache framework or Redis:

from django.core.cache import cache

cache_key = f"unique_field_pairs_{field}"
unique_pairs = cache.get(cache_key)

if not unique_pairs:
    unique_pairs = list(YourModel.objects.filter(field=str(field))\
                                        .exclude(tr__in=['some','some1'])\
                                        .values('field1', 'field2')\
                                        .distinct())
    cache.set(cache_key, unique_pairs, 3600)  # Cache for 1 hour

This turns subsequent requests into fast cache lookups instead of expensive database queries.

5. Fall Back to Native PostgreSQL SQL #

If the ORM still isn't performing as needed, use a raw SQL query to get full control over the execution plan. This lets you optimize the query exactly how PostgreSQL likes it:

from django.db import connection

with connection.cursor() as cursor:
    cursor.execute("""
        SELECT DISTINCT field1, field2
        FROM your_model_table_name
        WHERE field = %s
          AND tr NOT IN %s
    """, [str(field), ('some', 'some1')])
    unique_pairs = cursor.fetchall()  # Returns list of tuples (field1_value, field2_value)

Replace your_model_table_name with the actual database table name for your model (usually appname_modelname).

Quick Debugging Tip #

Use django-debug-toolbar to inspect the generated SQL, then run EXPLAIN ANALYZE on that query in PostgreSQL. This will show you if the query is using indexes, where the bottlenecks are (e.g., sequential scans), and guide further optimizations.

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