Django聚合函数是否使用索引？大表字段建索引能否加速Avg聚合？

Great question! Let's break this down into two key parts to address your concerns:

1. Will adding db_index=True to lines_per_page speed up the Avg aggregation? #

Short answer: Probably not much, if at all—unless you're in a specific edge case.

Here's why: Calculating an average requires scanning every single lines_per_page value in the table, summing them up, then dividing by the total number of rows. A standard B-tree index (which is what Django creates with db_index=True) keeps the field values sorted, but it doesn't reduce the number of entries you need to process. You still have to read every value to compute the sum and count.

The only exception here is if your query only needs the lines_per_page field (like your example, where you're aggregating across all rows without fetching other columns). In this case, PostgreSQL might opt for a covering index scan—since the index itself contains all the data needed for the query, it doesn't have to "jump back" to the main table to fetch rows. This can save a bit of I/O if your table has large other fields, but the performance gain is usually minimal compared to a full table scan, especially if most of your data is already in memory.

Also, keep in mind: Indexes add overhead to write operations (inserts/updates/deletes on the Blog model), so if you're doing a lot of writes, adding an index just for this aggregation might be a net loss.

2. Do Django's aggregation functions use indexes? #

Django doesn't force or block index usage—it simply translates your ORM code into raw SQL (for your example, that's something like SELECT AVG(lines_per_page) FROM blog_blog;). The decision to use an index is entirely up to PostgreSQL's query optimizer.

The optimizer will choose to use an index only if it determines that the index will make the query faster. To check if your aggregation is using an index, you can inspect the query plan:

• In Django, you can print the query plan with explain():

  queryset = Blog.objects.all().aggregate(x=Avg('lines_per_page'))
  print(queryset.query.explain())
  

• Or run the equivalent SQL directly in psql with EXPLAIN prepended, and look for terms like Index Scan in the output.

Practical Recommendations #

If your main goal is speeding up this full-table average calculation:

• Skip the index for this specific use case—it's unlikely to give meaningful gains and adds write overhead.
• Precompute the average instead: Use a periodic task (like Celery beat or Django's django-q) to calculate the average once every X minutes/hours, and store the result in a separate model or cache. This way, you avoid scanning 6GB of data every time you need the average.
• Consider a materialized view (PostgreSQL-specific): Create a materialized view that stores the precomputed average, and refresh it on a schedule. This is more efficient than recalculating from scratch every time.

If you're running aggregations with filters (e.g., Blog.objects.filter(name__startswith='A').aggregate(Avg('lines_per_page'))), then an index on lines_per_page might help—but only if the filter reduces the number of rows significantly. Even then, the index is helping with the filter, not the average calculation itself.

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