Introduction

This episode teaches you to measure real performance before optimizing: turn on PostgreSQL’s slow query log, attach django-silk to inspect each request, read EXPLAIN to find which query scans the whole table, then add an index in the right place. Following episode 11, where the expense tracker app was locked down on security, it’s time to see how fast or slow it runs under real load.

1. Turn On PostgreSQL’s Slow Query Log

PostgreSQL can log every query slower than a threshold you choose. Set log_min_duration_statement = 1000 in postgresql.conf to log every query running over one second, then read the log to see which queries repeat most often in the expense tracker app — usually the monthly transaction list page.

# postgresql.conf
log_min_duration_statement = 1000  # ms: ghi mọi query chạy trên 1 giây
log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d '

2. Attach django-silk to Inspect Each Request

The slow query log tells you which query is slow, but django-silk tells you which request produced it. Install silk, add the middleware and the URL, then open /silk/ to see each request with the time of every query, the number of duplicate queries, and even a detailed profile of the view.

# config/settings/prod.py
INSTALLED_APPS += ["silk"]
MIDDLEWARE += ["silk.middleware.SilkyMiddleware"]
SILKY_PYTHON_PROFILER = True
# config/urls.py
urlpatterns += [path("silk/", include("silk.urls", namespace="silk"))]

3. Read EXPLAIN Before Fixing

Once you have a slow query, run EXPLAIN ANALYZE in psql to see the execution plan: a Seq Scan on expenses_transaction means PostgreSQL reads the whole table, while an Index Scan means it uses an index. Watch the Actual Time column and Rows Removed by Filter — the more rows a filter removes, the more that condition deserves an index.

EXPLAIN ANALYZE
SELECT * FROM expenses_transaction
WHERE user_id = 1 AND date >= '2026-11-01'
ORDER BY date DESC;

4. Add Indexes in the Right Places

Don’t add an index for every column: each index slows INSERTs and costs disk space. Add indexes for columns used often in WHERE and ORDER BY — the (user, date) pair on the transaction table, for example — and use a composite index instead of two separate ones when a query filters on both columns at once.

# expenses/models.py
class Transaction(models.Model):
    user = models.ForeignKey(User, on_delete=models.CASCADE)
    date = models.DateField()
    amount = models.DecimalField(max_digits=12, decimal_places=2)
    class Meta:
        indexes = [
            models.Index(fields=["user", "date"]),
        ]

5. Measure Again and Keep the Profiling Habit

After adding the index, run EXPLAIN ANALYZE again to make sure the query now uses an Index Scan, then compare the timings before and after. Record the numbers in the project’s runbook: profiling isn’t a one-off job, it’s a habit every time you add a feature or the data doubles.

Apply It to Your Project

Apply it tonight: turn on the slow query log with a one-second threshold, install django-silk on staging, open the monthly transaction list page, then check /silk/ to find the slowest query. Run EXPLAIN ANALYZE on it, add an index for the filter column, measure again, and record the result in the runbook.

Related Posts

FAQ

Does django-silk slow down production?

Yes, a little: silk records every request and query in the database, so only enable it on staging, or temporarily on production while investigating, then turn it off. Never leave /silk/ open to the public — protect it with login or an IP allowlist.

How are EXPLAIN and EXPLAIN ANALYZE different?

EXPLAIN only shows the planned execution without running the query, while EXPLAIN ANALYZE actually runs it and reports the real time of each step. Use ANALYZE for diagnosis, but don’t run it on UPDATE or DELETE queries in production, because it really executes them.

What if the query is still slow after adding an index?

Check EXPLAIN again: the query may still scan the whole table because the WHERE clause applies a function to a column, for example DATE(created_at), which makes the index unusable. Then rewrite the query to compare directly on the column, or create an expression index if you really need it.

References