PartiQL: targeted reads

Part of the dialect series: the difference between a read that touches three items and one that reads — and bills — the whole table is a single predicate. This page is the WHERE clause: which predicates bound what DynamoDB reads, which merely filter it, and how to sort and page what comes back.

The Query/Scan divide

SELECT * FROM "stage.Outbox" WHERE pk = 'Outbox#123'        -- QuerySELECT * FROM "stage.Outbox" WHERE pk IN ('O#1', 'O#2')      -- key lookupsSELECT * FROM "stage.Outbox"WHERE pk = 'Outbox#123' AND begins_with(sk, 'EVT#')          -- narrowed QuerySELECT * FROM "stage.Outbox" WHERE Status = 'pending'        -- Scan (warned)

A partition-key equality or IN list in WHERE avoids the full read — DynamoDB's equality-or-IN rule. Equality executes as a Query; an IN list executes as one key lookup per value (an IN-driven read claims no sort-key narrowing — every other predicate filters after those lookups). Anything else degrades to a Scan that reads — and bills — every item. When the attribute you need to filter by isn't a key, the fix is a GSI whose partition key is that attribute: read it with the FROM "table"."index" form and the filter becomes a key condition again (multi-attribute GSIs are below).

The editor points at the exact predicate (or OR) responsible for a Scan, and with a live connection the warning is sized with the table's item count ("~432,926 items at last describe"). Tables outside your parsed schema still classify: their live key schema stands in, so pk IN (…) on an unmapped table reads as key lookups, not "can't classify".

SELECT pk, sk, Status
FROM "stage.Outbox"
WHERE Status = 'pending'

Three rows back, the whole table read to find them — that 0.0007% is the Scan tax. Now flip to the Keyed — Query variant: the same question with a partition-key equality in front reads 12 items instead of 432,926, and the strip turns green before anything runs.

And the tax compounds as the table grows. Drag the slider — the Scan's bill tracks the table, the keyed Query's doesn't:

WHERE: the predicate reference

All predicates are native. What varies is where they execute — in the key condition (bounding what DynamoDB reads) or in the filter (applied after the read; matched items are still read and billed):

• Comparisons — = <> != < <= > >=. Partition-key equality forms the key condition; sort-key comparisons on a queried target narrow it; anything else filters.
• BETWEEN low AND high — on the queried sort key it narrows the key condition itself; elsewhere it filters.
• IN ('a', 'b') — literal list. On the partition key it stays a key condition (one key lookup per value); never on a sort key; elsewhere it filters.
• AND — composes key conditions and filters freely. OR / NOT — can never form a key condition; their presence around key predicates degrades the read to a Scan, and the editor says so.
• IS MISSING / IS NOT MISSING — attribute absence. Absent is not the same as NULL: DynamoDB stores explicit nulls, and the two test differently.
• Functions — begins_with(path, prefix), contains(path, value), attribute_type(path, type), size(path). On a queried sort key, begins_with narrows the key condition; the rest always filter.

Multi-key GSIs

-- a multi-key GSI: two partition + two sort attributesSELECT * FROM "stage.Orders"."byRegionStatus"WHERE region = 'eu' AND tenant = 'acme'         -- equality on every partition attr  AND status = 'open' AND createdAt >= '2026'   -- sort attrs, left-to-right-- a gap, a missing partition equality, or a 2nd range → Scan

A GSI isn't limited to one partition key and one sort key. It can carry up to four partition and four sort attributes, kept separate and typed — not concatenated into one region#tenant#status string you have to parse back out. The studio classifies a read against one as a Query only when it honours the multi-key rule:

• Equality on every partition attribute — all of them, no exceptions; a missing one degrades to a Scan.
• Sort attributes bound left-to-right, no gaps — you may stop early, but you can't skip one and bind a later one.
• At most one trailing inequality — < <= > >= BETWEEN, or begins_with (which must come last). A second range degrades to a Scan.

A gap, a missing partition equality, or a second range turns the read into a Scan, and the strip names the attribute that broke the rule — so you see why it fell off the Query path, not just that it did.

SELECT orderId, region, status, createdAt
FROM "stage.Orders"."byRegionStatus"
WHERE region = 'eu' AND tenant = 'acme'
  AND status = 'open' AND createdAt >= '2026'

This is the green counterpart to the amber Scan above: same table, but every key attribute is pinned the way the rule wants, so the whole read is one targeted Query on the index. Drop the tenant equality or reorder the sort attributes in DynoStudio and the strip flips amber, naming what broke.

Sorting and paging

-- native: provably the queried sort key, server-side orderSELECT * FROM "stage.Users" WHERE pk = 'User#9' ORDER BY sk DESC-- lowered: per-page client sort, disclosedSELECT * FROM "stage.Users" ORDER BY Country ASC, Age DESC NULLS LASTLIMIT 50 OFFSET 10

• ORDER BY (native case) — a single key that provably is the queried target's sort key stays in the native statement, so DynamoDB returns rows in index order (ASC/DESC).
• ORDER BY (lowered case) — multi-key lists, non-key attributes, document paths (ORDER BY address.city), or unprovable targets sort each fetched page client-side, disclosed in the strip. Type order: booleans before numbers before text. Absent values sort as largest unless NULLS FIRST / NULLS LAST says otherwise.
• LIMIT n — core PartiQL that DynamoDB's dialect rejects; the studio sends it as the request's page limit.
• OFFSET n — skipped rows are fetched on top of the first page and dropped client-side. Skipped rows are still read and billed. First page only.
• DISTINCT — de-duplicates each fetched page client-side by deep value equality (numbers by value, sets order-insensitive). Like a client-side ORDER BY, it acts per page, so duplicates across page boundaries can reappear.

SELECT * FROM "stage.Outbox"WHERE pk = 'Outbox#123' AND Status = 'pending'

SELECT * FROM "stage.Outbox"WHERE pk = 'Outbox#123' AND sk BETWEEN 'EVT#0100' AND 'EVT#0200'

SELECT * FROM "stage.Orders"."byRegionStatus"WHERE region = 'eu' AND tenant = 'acme'  AND status = 'open' AND createdAt >= '2026'