Skip to Content

SELECT, WHERE, ORDER BY & LIMIT

When to use

Pull a filtered slice of rows out of a table in a stable, predictable order.

Analogy

SELECT is the librarian; WHERE is your search question; ORDER BY sorts the returned pile; LIMIT caps how many you carry home.

Data-flow diagram

        SELECT col1, col2
          FROM events
         WHERE user_id = 42              -- filter rows
      ORDER BY ts DESC                  -- stable order
         LIMIT 100                       -- cap returned rows
                |
                v
        result set (rows in defined order)

Deep explanation

Every Postgres query starts with SELECT and projects only the columns you actually need. WHERE filters row-by-row using boolean expressions and short-circuits on the first FALSE; push cheap predicates first. ORDER BY is not free: without an index, Postgres does an explicit sort. Always pair ORDER BY with LIMIT when the user only needs the top N — otherwise the planner will materialise every row before sorting, which quietly turns a 10-row request into a full-table scan. OFFSET-based pagination is fine for small N but degrades for deep pages; switch to keyset (cursor) pagination at scale.

Examples

Example 1

-- 3a: filter + order + cap
SELECT event_id, ts, payload
  FROM events
 WHERE user_id = 42
   AND ts >= NOW() - INTERVAL '7 days'
 ORDER BY ts DESC
 LIMIT 100;

LIMIT 100 keeps latency bounded; ORDER BY ts DESC hits an index on (user_id, ts) covering this exact shape.

Example 2

-- 3b: stable order matters for offline eval
SELECT chunk_id, content
  FROM rag_chunks
 ORDER BY chunk_id
 LIMIT 5 OFFSET 50;

OFFSET 50 is fine at 50 rows but at OFFSET 500000 Postgres still scans 500050 rows — keyset avoids that.

Example 3

-- 3c: keyset pagination beats OFFSET at depth
SELECT * FROM events
 WHERE user_id = 42
   AND ts < :last_seen_ts        -- cursor from previous page
 ORDER BY ts DESC
 LIMIT 100;

A cursor condition turns pagination into a range scan on the index instead of a discard-the-first-N rows operation.

Common mistake

SELECT * in production code. It copies every column over the wire, breaks when columns are added, and hides which fields downstream relies on. Always enumerate the columns. Also: don’t rely on row order without ORDER BY — without it, the planner may return rows in any order between versions.

Key takeaway

Project columns explicitly; pair ORDER BY with LIMIT; use keyset pagination for deep pages; trust the planner only when EXPLAIN ANALYZE agrees.

Production Failure Playbook

Failure scenario 1: select-star-blowup

Failure scenario 2: offset-pagination-degradation