Filtering Thousands of Records: PostgreSQL and Django ORM Performance

On a listings site, users filter by city, category, price range and free text. With a few hundred records everything feels fast. Past a few thousand, pages start to drag — and the cause is rarely in one place.

This post covers the four most common reasons listing pages slow down, and the fix for each.

1. Why does the N+1 query happen, and how do you prevent it?

This is the most frequent and most expensive mistake. Touch a related field in a template and Django issues a separate query per row:

listings = Listing.objects.filter(city=city)  # 1 query
 
for listing in listings:
    print(listing.category.name)  # +1 query per row

Fifty rows means fifty-one queries. Fetch the relations up front:

listings = (
    Listing.objects
    .filter(city=city)
    .select_related("category", "city")      # ForeignKey / OneToOne → JOIN
    .prefetch_related("images", "features")  # ManyToMany / reverse FK → separate query
)

The distinction matters:

• select_related uses a SQL JOIN. Right for single-valued relations (ForeignKey).
• prefetch_related issues a second query and joins in Python. Better for multi-valued relations, where a JOIN would multiply row counts.

To see queries during development:

from django.db import connection
print(len(connection.queries))

A more durable option is django-debug-toolbar, which shows every query and its duration per page.

2. Which fields should you index?

Without an index, PostgreSQL scans the whole table. Index the fields you filter on:

class Listing(models.Model):
    city = models.ForeignKey(City, on_delete=models.PROTECT)
    category = models.ForeignKey(Category, on_delete=models.PROTECT)
    price = models.DecimalField(max_digits=12, decimal_places=2)
    is_published = models.BooleanField(default=False)
    created_at = models.DateTimeField(auto_now_add=True)
 
    class Meta:
        indexes = [
            models.Index(fields=["is_published", "city", "-created_at"]),
            models.Index(fields=["price"]),
        ]

In a composite index, column order is critical. An index is usable left to right:

• Filtering by is_published → index used
• is_published + city → used
• Filtering by city alone → not used

So put the most selective column that appears in every query first. Above, is_published is in every listing query, hence position one.

Indexes aren't free: every INSERT and UPDATE must maintain them, and they consume disk. Don't index fields nobody queries.

3. Which text search method should you use?

icontains is easy but starts with a wildcard, so it can't use an index:

Listing.objects.filter(title__icontains=term)
# SQL: WHERE UPPER(title) LIKE UPPER('%term%')

Fine on small tables. As you grow, there are two alternatives.

Trigram index — typo-tolerant, "similar to" matching:

from django.contrib.postgres.indexes import GinIndex
from django.contrib.postgres.operations import TrigramExtension
 
class Migration(migrations.Migration):
    operations = [
        TrigramExtension(),  # installs pg_trgm
    ]

class Meta:
    indexes = [
        GinIndex(fields=["title"], name="listing_title_trgm",
                 opclasses=["gin_trgm_ops"]),
    ]

Full-text search — understands word stems, ranks by relevance:

from django.contrib.postgres.search import SearchVector, SearchQuery, SearchRank
 
vector = SearchVector("title", weight="A") + SearchVector("description", weight="B")
query = SearchQuery(term)
 
results = (
    Listing.objects
    .annotate(rank=SearchRank(vector, query))
    .filter(rank__gte=0.1)
    .order_by("-rank")
)

weight="A" makes a title match count for more than a description match.

Recomputing the vector on every query is expensive. In production, store it in a column and index it:

search_vector = SearchVectorField(null=True, editable=False)
 
class Meta:
    indexes = [GinIndex(fields=["search_vector"])]

For languages PostgreSQL has no dictionary for, the default english configuration will stem incorrectly. The simple dictionary doesn't stem at all — but it also doesn't stem wrongly, which is usually the better starting point.

4. Why does OFFSET pagination get slow?

Django's Paginator generates LIMIT/OFFSET. OFFSET 10000 means the database reads and discards ten thousand rows — the deeper the page, the slower the query.

For infinite scroll or "load more" interfaces, cursor-based pagination is far faster:

# First page
page = Listing.objects.order_by("-created_at", "-id")[:20]
 
# Next page: continue from the last row's values
page = (
    Listing.objects
    .filter(created_at__lt=last_created_at)
    .order_by("-created_at", "-id")[:20]
)

Including a unique column such as id in the ordering matters. If two rows share a created_at, the sort is unstable and rows can be skipped or repeated.

If you need numbered pages and the table is large, COUNT(*) becomes slow too. When an approximate figure is acceptable, reading PostgreSQL's own statistics is much cheaper:

SELECT reltuples::bigint FROM pg_class WHERE relname = 'listings';

How do you read EXPLAIN ANALYZE output?

Measure instead of guessing. In Django:

print(queryset.explain(analyze=True))

What to look for:

Seq Scan — the table is being read end to end. Normal on small tables; on a large one it means an index is missing.

Index Scan or Bitmap Index Scan — an index is in use, which is what you want.

rows=... actual rows=... — the gap between estimate and reality. A large gap means stale table statistics:

ANALYZE listings;

Nested Loop — fast on small sets, but between two large tables it may mean the planner made a poor choice.

Don't optimise before measuring

Every technique here adds complexity. Trigram indexes, full-text search and cursor pagination all make the code harder to follow.

The order should be:

1. Measure that it's slow (which page, how many ms)
2. Find why (EXPLAIN ANALYZE, query count)
3. Fix the single biggest cause
4. Measure again

In practice most listing pages reach acceptable speed with nothing more than select_related/prefetch_related and two well-chosen indexes. The rest is only needed when those aren't enough.