SELECT

SELECT * | <cols> FROM <table>
  [WHERE entity = '...' [AND ts ...] [AND <col> <op> <value>]...]
  [LATEST ON ts PARTITION BY entity]
  [ORDER BY ts [ASC|DESC]]
  [LIMIT <n>] [OFFSET <n>];

Range reads (immutable tables)

Rows are reconstructed: one row per timestamp where any selected channel has a real sample; other cells filled per channel policy (forward-fill). Selecting fewer channels therefore returns fewer rows — the timestamp union is over what you asked for.

SELECT entity, ts, speed, event FROM telemetry
  WHERE entity = 'veh-1' AND ts BETWEEN 1700000000000 AND 1700086400000;

Without an entity filter the scan covers every entity, ordered by ts across all of them.

Column filters

WHERE composes any number of column comparisons with AND, on every table kind — timeseries included:

SELECT entity, ts, row_key, point_speed FROM vehicle_device_events
  WHERE firm_id = 'c99…3fb' AND point_speed > 80;

SELECT entity, ts FROM telemetry WHERE status != 'ok';
SELECT entity, ts FROM telemetry WHERE event IS NOT NULL;

The operators are = != > >= < <= IS NULL IS NOT NULL. A comparison tests the reconstructed cell, so a FILL HOLD column matches on its carried value too, and staleness applies first. A row whose cell is absent matches nothing but IS NULL (!= included) — SQL-null semantics throughout. The same column may repeat to form a range (speed > 20 AND speed < 90).

Filters are engine-driven, not post-scan: the planner scans only the compared column first, reconstructs rows exclusively where it can match, skips whole blocks via per-block statistics (min/max value zone maps for integers and floats, Bloom filters for text), and fans the walk across cores. A selective equality on a declared INDEX column answers from the index without walking entities at all. An unordered LIMIT stops the scan as soon as it has its rows.

Distinct entities

SELECT DISTINCT entity lists a table's distinct entities and count(distinct entity) counts them — both answered from the entity index, without scanning any channel data:

SELECT DISTINCT entity FROM vehicle_device_events;
SELECT count(distinct entity) FROM vehicle_device_events;

DISTINCT applies to the entity dimension only (a fleet count, or the cohort behind a bucket) — not a general DISTINCT over arbitrary columns.

Device shadow

SELECT * FROM telemetry LATEST ON ts PARTITION BY entity [WHERE entity = '...'];

One row per entity: each channel at its stream tail, ts = the newest sample among the selected channels. Fully deleted entities do not appear.

Mutable tables

A plain SELECT on a MUTABLE table always resolves to the current version per entity (same shape as LATEST ON). Version history is not a query surface — it is merge fuel.

Spatial filters

A GEO column takes two WHERE predicates, AND-composable with the entity/ts/column filters on timeseries and rows tables:

-- map window: the bbox is (minLat, minLon, maxLat, maxLon)
SELECT entity, ts, speed FROM telemetry
  WHERE ts > 1700000000000 AND GEO_BBOX(pos, 40.8, 28.5, 41.3, 29.5);

-- radius in meters around a point (lat, lon)
SELECT name FROM depots WHERE GEO_DWITHIN(pos, 41.0151, 28.9795, 5000);

-- last known position per vehicle, spatially filtered
SELECT entity FROM telemetry
  WHERE GEO_BBOX(pos, 40.8, 28.5, 41.3, 29.5)
  LATEST ON ts PARTITION BY entity;

A row whose geo column has no value never matches (SQL-null semantics). count(*) takes the same predicates; SAMPLE BY does not combine with them yet.

Nearest-X (rows tables): with a GEO_DWITHIN anchor, the geo_distance pseudo-column projects the meters to its center and ORDER BY GEO_DISTANCE sorts by it — reverse-geocode and nearest-road queries in one statement:

SELECT name, place, geo_distance FROM osm_nodes
  WHERE GEO_DWITHIN(pos, 41.0086, 28.9802, 10000) AND place = 'suburb'
  ORDER BY GEO_DISTANCE LIMIT 3;

Counting

SELECT count(*) gives a row count. On rows tables it is the live current-state count (optionally filtered), resolved in one linear pass. On timeseries tables a bare count(*) streams the union-row count (see above); per-bucket counting lives under SAMPLE BY. A filtered count(*) prunes like any filter — it reconstructs only inside the intervals the compared column's block statistics allow — so a selective WHERE does not scan the whole table, and a statement whose client disconnects mid-scan is cancelled rather than left running.

Ordering, limits, pagination

ORDER BY ts (only ts) with ASC/DESC, then the pg pagination pair — LIMIT n [OFFSET m], either clause order:

SELECT ts, speed FROM telemetry WHERE entity = 'veh-1' ORDER BY ts DESC LIMIT 100;
SELECT ts, speed FROM telemetry WHERE entity = 'veh-1' LIMIT 100 OFFSET 200;  -- page 3

SELECT count(*) on a timeseries table streams the reconstruction and counts union rows — tie-group widths, tombstones and cross-run dedup all included, because the counter is the sweep. It composes with WHERE entity = …, a ts window and plain column filters, materializes nothing, and costs time proportional to the window it counts.

Row-returning SELECTs over pg-wire stream: rows leave in batches as the scan produces them, so an un-LIMITed export holds one batch in memory however many rows flow — killing the client (or a CancelRequest) cancels the scan mid-stream. ORDER BY ts without a LIMIT still materializes to sort and answers to the read budgets.

ORDER BY ts with a LIMIT reads in order: the engine finds the data's time edge from block metadata, walks time partitions from the LIMIT side (newest-first for DESC) and stops the moment enough rows are held — a "newest 100" on a billion-row table touches the newest blocks, not the table. Ordered reads without a LIMIT still materialize and sort, bounded by the read budgets.

OFFSET works on every table family (a kv scan pages the same way) and is what dashboard tools and ORMs generate; for large tables prefer the keyset idiom (WHERE ts > last here, WHERE x_rev > cursor on rows tables) — it stays cheap at any depth and no page shifts under concurrent writes.