How to Push a Report Into a SQL Join Query Instead of Python

Why filtering in Python is slow

The broken /reports endpoint pulls the entire table into memory, then filters and limits in a Python loop:

cur.execute("SELECT * FROM analytics")     # all 10,000 rows over the wire
all_rows = cur.fetchall()

category = request.args.get('category')
limit = request.args.get('limit', type=int)

results = []
for row in all_rows:
    time.sleep(0.001)                      # per-row processing overhead
    record = {...}
    if category and row["category"] != category:
        continue
    results.append(record)

if limit:
    results = results[:limit]              # limit applied AFTER the work

Two things kill it. First, SELECT * transfers all 10,000 rows even when the caller asked for limit=100. Second, the WHERE and LIMIT happen in Python after every row has already been fetched and processed - so the limit saves nothing. At roughly 1ms per row that is 10 seconds of pure overhead, and the endpoint times out.

The database is a query engine built for exactly this. The fix is to push the filter and limit into the SQL query so PostgreSQL returns only the rows you need.

Push the filter and limit into the SQL query

Read the query parameters first, then build the query so category becomes a WHERE clause and limit becomes a LIMIT. Parameterize both with %s placeholders so psycopg2 escapes the values - never string-format user input into SQL:

@app.route('/reports')
def get_reports():
    category = request.args.get('category')
    limit = request.args.get('limit', type=int)

    conn = get_db()
    cur = conn.cursor()

    sql = "SELECT id, event, category, value, created_at FROM analytics"
    params = []
    if category:
        sql += " WHERE category = %s"
        params.append(category)
    sql += " ORDER BY id"
    if limit:
        sql += " LIMIT %s"
        params.append(limit)

    cur.execute(sql, params)
    rows = cur.fetchall()
    cur.close()
    conn.close()

    results = [{
        "id": r["id"],
        "event": r["event"],
        "category": r["category"],
        "value": float(r["value"]),
        "created_at": str(r["created_at"]),
    } for r in rows]

    return jsonify({"reports": results, "count": len(results)})

Now GET /reports?limit=100 sends SELECT ... LIMIT 100 to Postgres, which returns 100 rows, not 10,000. The Python-side time.sleep() loop is gone entirely - there is nothing to iterate over except the rows you actually want. The same query joins the category filter into the WHERE clause, so ?category=sales&limit=50 returns only sales rows.

Verify the speedup

Time both requests from the workspace terminal:

# The slow path timed out; now it should return in tens of milliseconds
time curl -s "http://localhost:5000/reports?limit=100" | python3 -m json.tool | head

# Category filter is applied in SQL, not Python
curl -s "http://localhost:5000/reports?category=sales&limit=50" \
  | python3 -c "import sys,json; d=json.load(sys.stdin); print(d['count'])"

A GET /reports?limit=100 that took 10s+ now returns in around 50ms. If you want to confirm Postgres is doing the work, prefix the query with EXPLAIN ANALYZE in psql - the LIMIT node caps the scan, and with an index on category the WHERE filter uses an index scan instead of reading the whole table.