Filtering Thousands of Records: PostgreSQL and Django ORM

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 (the list is only filled when DEBUG = True):

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.

The same pattern shows up in Django's admin changelist: a list_display entry that reaches through a relation — a custom method or property that reads obj.city.region, say — triggers one query per row. Plain ForeignKey columns are handled for you (with list_select_related left at its default, Django applies select_related when list_display contains a ForeignKey), but anything deeper needs an explicit list_select_related or a get_queryset override. Turning Django admin into a CMS covers the rest of what makes that list view usable.

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. A B-tree index is most efficient when the query constrains its leading columns:

• Filtering by is_published → efficient
• is_published + city → efficient
• Filtering by city alone → the planner can still use the index, but has to scan all of it, so it is rarely a good plan

A practical rule: put the columns you compare with equality first, and the column you sort or range-filter on last. Above, is_published and city are equality filters and -created_at is the sort, so one index serves "newest listings in this city" without a separate sort step. Note that Django already creates a single-column index for every ForeignKey, so city alone is covered anyway; the composite index earns its keep on the combined query.

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 the pattern starts with a wildcard, so an ordinary B-tree index can't help:

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

Fine on small tables. As you grow, there are two alternatives. Mind the SQL above: a trigram index only helps icontains if it is built on the same expression (Upper("title")), so an index on the bare title column is not enough for that exact query.

Trigram index — typo-tolerant, "similar to" matching (the pg_trgm extension can also speed up LIKE '%term%' when the index expression matches):

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"])]

A stored column does not update itself: refresh it in save(), a database trigger or a periodic job, or search results will go stale.

Pass the same config to SearchVector and SearchQuery; if you omit it, PostgreSQL's default_text_search_config setting decides, and a mismatch between the two sides gives odd results. For languages without a built-in dictionary, an 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
from django.db.models import Q
 
page = (
    Listing.objects
    .filter(
        Q(created_at__lt=last_created_at)
        | Q(created_at=last_created_at, id__lt=last_id)
    )
    .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 — which is also why the filter compares id when the timestamps are equal.

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';

Use your real table name (Django's default is app_model). The figure is only as fresh as the last ANALYZE or autovacuum, and a table that was never analyzed reports -1.

If this endpoint is called from a Next.js Server Component alongside other independent data — the listing plus a separate facet-counts call, say — fetch them with Promise.all instead of one await after another. Fetching without waterfalls covers that failure mode.

How do you read EXPLAIN ANALYZE output?

Measure instead of guessing. In Django:

print(queryset.explain(analyze=True))

analyze=True actually executes the query, so be careful with queries that are slow or have side effects.

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.