Postgres – SELECT, WHERE and ORDER BY

Every query in this post runs against a booking database with 20,000 properties and 400,000 bookings in it. The syntax is the easy part; the parts that go wrong are NULL, operator precedence, and pagination.

SELECT

SELECT id, title, city, price_per_night
FROM   properties
WHERE  city = 'Lisbon' AND status = 'PUBLISHED'
ORDER  BY price_per_night
LIMIT  5;

Name your columns. SELECT * is fine at a psql prompt and a liability in application code: it breaks when a column is added, ships columns nobody reads across the network, and stops an index-only scan being possible. It also hides the moment when someone adds a text column holding a megabyte of description to a table you select from in a loop.

WHERE, and the parenthesis that changes the answer

AND binds tighter than OR. These are different queries:

-- "cabins anywhere, plus anything in Oslo"  — probably not what was meant
SELECT count(*) FROM properties
WHERE  property_type = 'CABIN' AND status = 'PUBLISHED' OR city = 'Oslo';

-- "published cabins, in Oslo or Bergen"
SELECT count(*) FROM properties
WHERE  status = 'PUBLISHED' AND property_type = 'CABIN' AND city IN ('Oslo', 'Bergen');

The first returns every unpublished Oslo property too. Parenthesise anything mixing AND with OR, even when you are sure.

The operators worth knowing

BETWEEN on dates and timestamps is a trap. It includes both endpoints, so a range over timestamps catches midnight exactly and nothing else that day:

-- wrong: loses everything after midnight on the 30th
SELECT count(*) FROM bookings
WHERE  created_at BETWEEN '2024-06-01' AND '2024-06-30';

-- right: half-open, and it is the habit to keep
SELECT count(*) FROM bookings
WHERE  created_at >= '2024-06-01' AND created_at < '2024-07-01';

Half-open ranges also compose: consecutive months cover every instant exactly once, with no gap at midnight and no double counting.

NULL will surprise you three times

SELECT count(*) AS all_rows,
       count(*) FILTER (WHERE cancelled_at IS NULL)     AS never_cancelled,
       count(*) FILTER (WHERE cancelled_at IS NOT NULL) AS cancelled
FROM   bookings;

1. A comparison with NULL is NULL, not false. WHERE status <> 'CANCELLED' drops rows where status is NULL, because NULL is not true.
2. NOT IN with a NULL in the list returns nothing at all. x NOT IN (1, 2, NULL) can never be true. Use NOT EXISTS.
3. NULL sorts last by default in ORDER BY ... ASC and first descending, which silently reverses which rows appear on page one.

SELECT id, cancelled_at FROM bookings
ORDER  BY cancelled_at DESC NULLS LAST
LIMIT  5;

Sorting

SELECT city, price_per_night, rating_average
FROM   properties
WHERE  status = 'PUBLISHED'
ORDER  BY city ASC, price_per_night DESC
LIMIT  10;

Sort order on text depends on the database's collation, which is fixed when the cluster is created. If you need a specific, portable ordering, ask for it:

SELECT city FROM properties ORDER BY city COLLATE "C" LIMIT 10;

And when a query has LIMIT but no ORDER BY, the rows you get are whatever came out first. It will look stable in testing and change the day the planner picks a different scan.

The shape of a WHERE clause decides whether an index can be used

Two queries that return the same rows, and only one of them can use an index on check_in:

-- the column is wrapped in a function, so an index on check_in cannot be used
SELECT count(*) FROM bookings WHERE date_part('year', check_in) = 2024;

-- the column is bare on one side, compared against a constant
SELECT count(*) FROM bookings WHERE check_in >= '2024-01-01' AND check_in < '2025-01-01';

An index stores the values of check_in, not the values of date_part('year', check_in). Once the column is inside a function call, the index no longer describes what you are asking about, and Postgres has to compute the expression for every row.

The rule generalises. WHERE lower(email) = 'a@b.com', WHERE amount::text LIKE '1%' and WHERE created_at + interval '1 day' > now() all have the same problem. Keep the column bare on one side of the comparison and move the arithmetic to the other — or, when the function really is how you query, build an index on the expression itself, which the indexes post covers.

LIKE has its own version of this: a pattern anchored at the start can use a b-tree, and one that begins with a wildcard cannot.

SELECT count(*) FROM properties WHERE title LIKE 'Sunny%';   -- can use an index
SELECT count(*) FROM properties WHERE title LIKE '%loft%';   -- cannot

DISTINCT, and DISTINCT ON

SELECT DISTINCT city FROM properties ORDER BY city;

DISTINCT ON is a Postgres extension and one of its most useful: keep the first row of each group, where "first" is decided by the ORDER BY.

-- the cheapest published property in each city
SELECT DISTINCT ON (city) city, id, title, price_per_night
FROM   properties
WHERE  status = 'PUBLISHED'
ORDER  BY city, price_per_night ASC, id;

The rule that catches everyone: the leading ORDER BY expressions must match the DISTINCT ON list. Sort by price_per_night first and Postgres rejects the query rather than guessing.

Pagination that does not get slower

SELECT id, title FROM properties ORDER BY id LIMIT 20 OFFSET 10000;

That reads 10,020 rows and throws 10,000 away. Page 1 is instant, page 500 is not, and the cost grows with the page number — which is why deep pages of a listing feel broken while the first page feels fine.

Keyset pagination remembers where the last page ended instead of counting from the start:

-- first page
SELECT id, title FROM properties ORDER BY id LIMIT 20;

-- next page: pass the last id you saw
SELECT id, title FROM properties WHERE id > 5820 ORDER BY id LIMIT 20;

Every page costs the same, because the index seeks straight to the starting point. The trade-off is that you cannot jump to page 500 — which is fine, since nobody does — and that the sort key must be unique. For a non-unique sort, compare on the pair:

SELECT id, title, price_per_night
FROM   properties
WHERE  (price_per_night, id) > (120.00, 5820)
ORDER  BY price_per_night, id
LIMIT  20;

That row-value comparison is doing real work: it means "price greater, or price equal and id greater", written once and matching an index on (price_per_night, id) exactly.

LIMIT without ORDER BY is not a shortcut

Worth stating on its own because it is the most common accidental bug in this post's subject. A LIMIT with no ORDER BY does not mean "any 20 rows, I do not care" — it means "whichever 20 the plan happens to emit first", and that changes when the table grows, when an index is added, or when autovacuum last ran.

The consequence is a paginated list that skips and repeats rows between pages while every individual query looks correct. If the order genuinely does not matter, sort by the primary key anyway; it is nearly free when the index is already there and it makes the result reproducible.

CASE and COALESCE

SELECT id,
       COALESCE(state, country)         AS region,
       CASE WHEN price_per_night < 100  THEN 'budget'
            WHEN price_per_night < 300  THEN 'mid'
            ELSE 'premium' END          AS bracket
FROM   properties
LIMIT  5;

COALESCE returns its first non-NULL argument. CASE is evaluated in order and stops at the first match, so overlapping conditions are resolved top-down — put the narrowest first.