Reading performance & plans

Sargability, plan smells (scan/join strategy), SELECT* cost on columnar, spill, row-goal effects.

• ★Sargability

  A function-wrapped predicate (WHERE date(ts) = ..., UPPER(email) = ...) defeats index/partition pruning — rewrite it as a range.

  Lesson →
  See it breakshowhide

  Setup

  CREATE TABLE logins(id INT, ts TIMESTAMP); INSERT INTO logins VALUES (1, TIMESTAMP '2024-03-05 08:00'), (2, TIMESTAMP '2024-03-05 22:00'), (3, TIMESTAMP '2024-03-06 01:00');

  The trap

  SELECT id FROM logins WHERE CAST(ts AS DATE) = DATE '2024-03-05' ORDER BY id;

  1

  2

  The fix

  SELECT id FROM logins WHERE ts >= TIMESTAMP '2024-03-05' AND ts < TIMESTAMP '2024-03-06' ORDER BY id;

  1

  2

  Both return the same two logins — but wrapping the column in CAST(ts AS DATE) hides it from the index, so the query scans every row instead of pruning.

• Reading a plan for two smells

  A full scan where pruning was expected, and the wrong join strategy (broadcast vs shuffle/hash), are the two big plan smells.

  Lesson →
  See it breakshowhide

  Setup

  CREATE TABLE plays(machine VARCHAR, score INT); INSERT INTO plays VALUES ('a',10),('b',999),('c',20),('d',5),('e',30);

  The trap

  SELECT machine FROM plays WHERE score + 0 > 900 ORDER BY machine;

  b

  The fix

  SELECT machine FROM plays WHERE score > 900 ORDER BY machine;

  b

  Both queries return the same machine, but wrapping the column in score + 0 hides it from any prune — the plan shows a SEQ_SCAN reading all five rows to keep one, smell one of a slow query.

• SELECT * cost on columnar

  On a columnar engine, projection is the bill — SELECT * scans every column (bridge to scanned-bytes).

  Lesson →
  See it breakshowhide

  Setup

  CREATE TABLE tele(cust VARCHAR, plan VARCHAR, city VARCHAR); INSERT INTO tele VALUES ('c1','pro','Rome');

  The trap

  SELECT * FROM tele WHERE plan = 'pro';

  c1

  pro

  Rome

  The fix

  SELECT city FROM tele WHERE plan = 'pro';

  Rome

  The two-column telemetry question read every column — on a columnar engine SELECT * scans all three column streams to answer what one (city) needed; naming the column projects only it.

• Spill

  Sorts/joins that exceed memory spill to disk — the ORDER-BY-everything habit is expensive.

  Lesson →
  See it breakshowhide

  Setup

  CREATE TABLE inv(item VARCHAR, qty INT, note VARCHAR); INSERT INTO inv VALUES ('a',3,'x'),('b',9,'y'),('c',5,'z');

  The trap

  SELECT item, qty, note FROM inv ORDER BY qty DESC;

  b	9	y
  c	5	z
  a	3	x

  The fix

  SELECT item FROM inv ORDER BY qty DESC LIMIT 3;

  b

  c

  a

  The export sorted every column of every row when only the top items were read — a sort holds its whole working set in memory, so an ORDER-BY-everything on a big wide table spills to disk (or errors under a cap); narrowing and LIMITing sorts far less.

• Row-goal effects & pagination

  LIMIT can change the plan; OFFSET pagination re-scans, keyset pagination doesn't.

  Lesson →
  See it breakshowhide

  Setup

  CREATE TABLE sightings(name VARCHAR, bright INT); INSERT INTO sightings VALUES ('a',3),('b',9),('c',5),('d',1),('e',7);

  The trap

  SELECT name FROM sightings ORDER BY bright DESC;

  b

  e

  c

  a

  d

  The fix

  SELECT name FROM sightings ORDER BY bright DESC LIMIT 3;

  b

  e

  c

  The full sort returned every sighting when only the brightest 3 were wanted — LIMIT is a row goal the optimizer honors where the pipeline streams (ORDER BY + LIMIT becomes a bounded TOP_N heap), but it can't help an aggregate whose first row needs all input.

• CTE materialization fences

  Where the engine materializes a CTE, it becomes an optimization fence (dialect note; P08 crossover).

  Lesson →
  See it breakshowhide

  Setup

  CREATE TABLE tallies(day INT, n INT); INSERT INTO tallies VALUES (1,5),(7,3),(8,9);

  The trap

  WITH t AS (SELECT day, n FROM tallies) SELECT n FROM t WHERE day = 7;

  3

  The fix

  SELECT n FROM tallies WHERE day = 7;

  3

  Both return the one day's tally, but where an engine materializes the CTE it becomes an optimization fence — the outer WHERE day = 7 can't push inside, so the CTE scans every row into a temp result, then filters one; DuckDB inlines and pushes the filter into the scan.