# PartiQL: targeted reads

Part of the [dialect series](https://dynostudio.dev/docs/dynostudio-partiql/): the difference between a read that touches three items and one that reads — and bills — the whole table is often a single predicate. Start with the ordinary Query/Scan path, then use the same editor for native vector similarity and explicitly parallelized scans — each with its own cost and guardrails disclosed before the wire.

**You'll learn**

- Tell a **key condition** (bounds what DynamoDB reads) from a **filter** (applied after — still billed)
- Keep a read a cheap **Query**, and recognise the predicates that degrade it to a **Scan**
- Read a **GSI** as a target — including **multi-key** GSIs with several partition and sort attributes
- Run a top-k **vector similarity search** as one native `SearchVectors` request
- Sort and page a result set, and know which rows you still pay for
- Use `PARALLEL n` when an intentional Scan should finish sooner — without mistaking faster for cheaper

## The Query/Scan divide

```sql
SELECT * FROM "stage.Outbox" WHERE pk = 'Outbox#123';       -- Query
SELECT * FROM "stage.Outbox" WHERE pk IN ('O#1', 'O#2');     -- key lookups
SELECT * FROM "stage.Outbox"
WHERE pk = 'Outbox#123' AND begins_with(sk, 'EVT#');         -- narrowed Query
SELECT * 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".

**Try it · PartiQL**

_Filter only — Scan_

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

_Executes as:_ Scan · stage.Outbox — No key condition — every item is read and billed, then filtered. The matched rows are a sliver of what you pay for. Switch to the keyed variant to see the same question asked for the price of one partition.

_Keyed — Query_

```sql
SELECT pk, sk, Status
FROM "stage.Outbox"
WHERE pk = 'Outbox#123' AND Status = 'pending'
```

_Executes as:_ Query · stage.Outbox — The pk equality forms the key condition — one partition read; Status filters within it. Same question, keyed: items read drop from ~432,926 to 12.

_Run it live in DynoStudio:_ https://dynostudio.dev/dynostudio/

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:

_Interactive on the web page: the Scan-tax slider — drag the table size and compare what a Scan vs a keyed Query bills. https://dynostudio.dev/docs/dynostudio-partiql-reads/_

## WHERE: the predicate reference

For ordinary table and GSI Query/Scan reads, all of these 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

```sql
-- a multi-key GSI: two partition + two sort attributes
SELECT * 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.

**Try it · Multi-key GSI**

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

_Executes as:_ Query · stage.Orders.byRegionStatus — Equality on both partition attributes, then both sort attributes left-to-right with one trailing range — a single targeted Query on the index. Native: a multi-key GSI binds up to 4 partition + 4 sort attributes, kept separate and typed.

_Run it live in DynoStudio:_ https://dynostudio.dev/dynostudio/

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](https://dynostudio.dev/dynostudio/) and the strip flips amber, naming what broke. (Following along on the [sample tables](https://dynostudio.dev/docs/dynostudio-partiql-setup/)? `byRegionStatus` is the one-statement optional step from the setup page — run it and this exact query returns these exact two orders.)

## Vector similarity search

```sql
SELECT ProductId, Title,
       VECTOR_DISTANCE(Embedding, {queryVector}) AS score
FROM "Products"."ProductEmbeddingIndex"
WHERE Category = {category} AND Brand = 'Acme'
ORDER BY VECTOR_DISTANCE(Embedding, {queryVector}) ASC
LIMIT 10
```

`VECTOR_DISTANCE` targets a live DynamoDB vector index and lowers to **one native, eventually consistent `SearchVectors` request**. The returned score comes from the service; DynoStudio never recomputes it client-side. When selected, the score needs an explicit, non-colliding `AS` alias. `COSINE` and `EUCLIDEAN` use `ASC` for nearest-first, while `DOT_PRODUCT` uses `DESC`; omitting the direction uses the index's nearest-first order.

- **Vector binding** — `{queryVector}` is a numeric PartiQL list such as `[0.12, -0.34, 0.56]`. Its length must exactly match the index dimensions, and every value must be a finite 32-bit number. History stores only its digest and dimension count, then asks you to bind the vector again on replay.
- **Top-k bound** — `LIMIT` is required and must be `1`–`100`. One request returns one page.
- **Search schema** — a `HASH` attribute declared by `PARTITION BY` must be pinned with `=`. Optional conditions may use only top-level `INLINE_FILTER` attributes declared by `FILTER BY`, also joined with `=` and `AND`.
- **Projection** — select `*`, projected top-level attributes, and optionally one aliased score. Fetching a non-projected base-table attribute needs a separate join-back shape.
- **Readiness and cost** — the table must be on-demand and the index must be `ACTIVE` with backfill finished. Vector search is byte-metered rather than RCU-metered; the receipt reports `VectorSearchRequestBytes`.

> **Vector WHERE is deliberately narrow:** `OR`, `NOT`, document paths, ranges, `IN`, `contains`, `begins_with`, and `MATCH` are refused before the wire for this shape. If the connected store or AWS partition has not confirmed vector support, the editor keeps the capability gated instead of guessing.

> **Vector search uses dedicated AWS endpoints:** If ordinary DynamoDB browsing works but vector search reports an unreachable endpoint, allow the dedicated `search-ddb` / `search-dynamodb` hostnames through DNS, proxies, TLS inspection, and private-network policy. Reaching the classic DynamoDB hostname alone does not prove this path is reachable.

The editor uses the current `DescribeTable` descriptor to complete vector-index names, the vector and search-schema attributes, dimensions, distance metric, projection, and readiness. To create the target itself, use [`CREATE VECTOR INDEX`](https://dynostudio.dev/docs/dynostudio-vector-index/).

## Sorting and paging

```sql
-- native: provably the queried sort key, server-side order
SELECT * FROM "stage.Users" WHERE pk = 'User#9' ORDER BY sk DESC;
-- lowered: per-page client sort, disclosed
SELECT * FROM "stage.Users" ORDER BY Country ASC, Age DESC NULLS LAST
LIMIT 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.

## PARALLEL n — faster scans, same bill

```sql
SELECT * FROM "stage.Orders"
WHERE status = 'FAILED' PARALLEL 4;

SELECT region, SUM(order.total)
FROM "stage.Orders" GROUP BY region PARALLEL 4;

PROFILE TABLE "stage.Orders" PARALLEL 4;
```

A trailing `PARALLEL n` runs an intentional Scan as **n concurrent DynamoDB segments** and merges them client-side. It can shorten wall-clock time for a profile, full-table aggregate, or sweep, but it reads the same items and bills the same total RCUs as a serial Scan — while consuming that capacity at up to **n× concurrently**. The Executes-as strip discloses the fan-out and throttle risk before the run.

- **Scans only** — on a key-condition Query it is a no-op. Base tables and GSIs are supported; a parallel GSI scan is eventually consistent.
- **Pushable filters only** — comparison, `BETWEEN`, `begins_with`, `IN`, `IS [NOT] MISSING`, and boolean composition can stay parallel. Unsupported predicates, a client-side `ORDER BY`, subqueries, or set operations run serially with the reason shown.
- **Bounded output** — `LIMIT` stops the fan-out when enough rows arrive, so which rows win is nondeterministic just as it is for a serial Scan. Without `LIMIT`, a plain parallel `SELECT` returns one fast page; aggregates and `PROFILE TABLE` drain and fold the full scan.
- **Guarded concurrency** — Block Scans still blocks it. The degree is capped in Settings → Querying (default `4`); above the configured cap it refuses. Parallel scan acceleration is a Professional capability.

**AWS background**

- [Querying tables in DynamoDB](https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/Query.html) — AWS docs: partition-key equality, optional sort-key narrowing, and pagination.
- [Scanning tables in DynamoDB](https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/Scan.html) — AWS docs: Scan reads table or index data before filter expressions discard results.

**Recap**

- For ordinary table and GSI reads, a **partition-key equality or `IN`** is the only thing that turns the access path into a cheap Query; everything else Scans.
- On a queried sort key, `begins_with` / `BETWEEN` / comparisons **narrow** the read; every other predicate **filters after** it (matched rows still billed).
- A **GSI** — including a multi-key one (up to 4 partition + 4 sort attributes) — reads with `FROM "table"."index"`, and is Query-or-Scan by the same rule.
- A vector-index `SELECT` lowers to one byte-metered `SearchVectors` request; `LIMIT 1..100`, descriptor-valid filters, and a ready index are required.
- `OFFSET`-skipped rows are still read and billed; client-side `ORDER BY` and `DISTINCT` act **per fetched page**.
- `PARALLEL n` makes a Scan faster, not cheaper, and can consume capacity up to **n× concurrently**.

**Check yourself**

**1. Which `WHERE` clause keeps a read of `stage.Outbox` (keys `pk`, `sk`) a cheap **Query**?**

- `WHERE pk = 'Outbox#123' AND Status = 'pending'` ✓ — the `pk` equality forms the key condition, and `Status` filters *within* that one partition.
- `WHERE Status = 'pending'` — no key condition — this Scans, reading (and billing) every item before the filter runs.
- `WHERE pk = 'Outbox#123' OR pk = 'Outbox#124'` — `OR` can never form a key condition. Write it as `pk IN ('Outbox#123', 'Outbox#124')` to get key lookups.

**2. A read runs `LIMIT 50 OFFSET 100`. How many rows do you pay for?**

- 150 — the `OFFSET` rows are fetched and dropped ✓ — skipped rows are read on top of the first page and discarded client-side. **Skipped rows are still read and billed.**
- 50 — only the returned page — the 100 `OFFSET` rows don't vanish: they're fetched first, then dropped client-side — and billed.
- 100 — the offset is free, the limit bills — it's the reverse: everything read bills, and `OFFSET` forces 100 extra reads before your 50.

**3. A multi-key GSI has partition attributes (`region`, `tenant`) and sort attributes (`status`, `createdAt`). Which read is a **Query**?**

- `region = 'eu' AND tenant = 'acme' AND status = 'open' AND createdAt >= '2026'` ✓ — equality on **every** partition attribute, sort attributes bound left-to-right, one trailing range — the multi-key rule, honoured.
- `region = 'eu' AND status = 'open'` — the `tenant` partition attribute is missing — equality on *every* partition attribute is required, so this Scans.
- `region = 'eu' AND tenant = 'acme' AND createdAt >= '2026'` — the sort attributes bind left-to-right with no gaps — skipping `status` to bind `createdAt` degrades the read to a Scan.

**4. What does `PARALLEL 4` change about an intentional full-table Scan?**

- Wall-clock time and concurrent capacity — not total items or RCUs ✓ — four native Scan segments can run concurrently, but the same table data is still read and billed.
- It divides the RCU bill by four — parallelism changes scheduling, not the amount of data DynamoDB reads.
- It turns the Scan into a Query — only a valid key condition changes the access path; `PARALLEL` remains a segmented Scan.

**Try it yourself**

**1. Turn a Scan into a Query**

This reads the whole table: `SELECT * FROM "stage.Outbox" WHERE Status = 'pending'`. Narrow it to a single partition.

_Hint:_ A Scan becomes a Query the moment a partition-key equality joins the WHERE. `Status` can stay — as a filter.

_Solution:_ Add a `pk` equality: `pk = …` forms the key condition (a Query); `Status = …` then filters *within* that partition.

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

**2. Narrow with a sort-key range**

Within one partition, return only the events whose sort key falls in a range.

_Hint:_ On a queried sort key, `BETWEEN` narrows the key condition itself — it doesn't just filter.

_Solution:_ `BETWEEN` on the sort key narrows the read server-side, so DynamoDB only touches the matching slice — not the whole partition.

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

**3. Query a multi-key GSI**

Read `"stage.Orders"."byRegionStatus"` for open orders in region `eu`, tenant `acme`, created since 2026.

_Hint:_ Equality on every partition attribute (`region`, `tenant`), then the sort attributes left-to-right (`status`, `createdAt`).

_Solution:_ All partition attributes use equality and the sort attributes bind left-to-right with one trailing range — so it stays a Query on the index.

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

> **Where next?:** One table, read precisely. [Joins, subqueries & sets](https://dynostudio.dev/docs/dynostudio-partiql-joins/) reads *across* tables — and across the entities of a single-table design — with requests DynamoDB can't compose on its own.

---
_Source: https://dynostudio.dev/docs/dynostudio-partiql-reads/ · DynoStudio Docs_