Advanced Indexing (PTI)

QueryIndexingPerformance

Stable On by default · production-ready

What it is

A classical index is good at finding rows, but to compute a sum or a count the engine still has to visit the matching rows and add up cells.

The Polyphonic Tensor Index (PTI) is different. It maintains exact, pre-computed aggregates per group — sum, count, avg, min/max — and skips data that provably can’t match. When a grouped aggregate is covered by the index, the engine returns the answer without scanning the table at all.

You create a PTI — and every other index type — through the normal CREATE INDEX surface, so it fits into your existing schema workflow. AetheriusDB indexes come in a few families, each with a specific job:

USING …	Family	What it accelerates
PTI … INCLUDE (…)	Covering aggregate index	Grouped GROUP BY aggregates, answered without scanning the table
PTI / BTREE / HASH	Physical tree / hash	UNIQUE enforcement (probe, not scan) and sort-merge join eligibility
HNSW / IVFFLAT	Vector	k-nearest-neighbour over TENSOR(N) columns — see Vector Search
CREATE GRAPH INDEX	Graph / Join-Graph	Selective joins along a declared relationship — see Graph Index

Why it matters

Aggregations that classical indexes accelerate only modestly — sums, counts, averages and min/max rolled up by an indexed column — return nearly instantly. Point lookups and range scans on huge tables touch only a tiny fraction of the data.

• Covered aggregation — a covered GROUP BY reads zero table rows.
• Exact pruning — the index proves whole regions of the table can’t match and skips them, with no false positives.
• Exact-or-scan — covered aggregates are maintained on the write path and a covered read never returns a stale value; if an answer can’t be proven exact, the query transparently falls back to a scan.

flowchart LR
  classDef q   fill:#1f2640,stroke:#7c5cff,color:#e8eaf0
  classDef idx fill:#1f2640,stroke:#00d4ff,color:#e8eaf0
  classDef ok  fill:#1f2640,stroke:#4ade80,color:#4ade80
  classDef alt fill:#1f2640,stroke:#ff5577,color:#ff8899

  Q[GROUP BY query<br/>e.g. SUM revenue by status]:::q --> P{Covered by<br/>the PTI?}:::idx
  P -- yes --> A[Answer served from<br/>pre-computed aggregates<br/>zero table rows read]:::ok
  P -- no --> S[Normal plan runs<br/>same correct result]:::alt

The covering PTI: aggregates without a scan

Create a PTI with familiar CREATE INDEX syntax, and add an INCLUDE clause to choose which aggregates to pre-compute.

-- A PTI over the status column, with exact pre-computed aggregates over revenue.
CREATE INDEX idx_orders_status
  ON orders (status)
  USING PTI
  INCLUDE (SUM(revenue), AVG(revenue), MIN(revenue), MAX(revenue), COUNT(*));

-- A grouped aggregate over the indexed column is answered from the
-- index instead of scanning the table:
SELECT status, SUM(revenue), COUNT(*)
FROM   orders
GROUP BY status;

The covering fast-path applies to a single-table GROUP BY whose grouping columns are exactly the index’s columns and whose projections are those columns plus aggregates the INCLUDE list covers. An ORDER BY, LIMIT, or OFFSET on top is also served from the index — the grouped result is sorted and truncated in memory, so a “top-N by revenue” query avoids the table scan. Anything richer — a HAVING, a join, DISTINCT, or an aggregate that isn’t in the INCLUDE list — simply runs the normal plan and returns the same correct result.

Covered aggregates: SUM, COUNT(*), COUNT(col), AVG, MIN, MAX (all exact), and APPROX_COUNT(col) (an approximate distinct count — see below). APPROX_PERCENTILE / quantile digests are not yet supported in INCLUDE and are rejected at CREATE INDEX time rather than silently accepted. Result types match the scan path exactly: COUNT returns an integer, and SUM/AVG/MIN/ MAX/APPROX_COUNT return floating-point.

Composite (multi-column) group keys

An INCLUDE index over several columns covers a GROUP BY on that exact set of columns. GROUP BY is order-insensitive — the index matches whether you write GROUP BY region, status or GROUP BY status, region.

CREATE INDEX idx_sales_region_status
  ON sales (region, status)
  USING PTI
  INCLUDE (SUM(amount), COUNT(*), MIN(amount), MAX(amount));

-- Covered — answered per (region, status) group without a table scan:
SELECT region, status, SUM(amount)
FROM   sales
GROUP BY region, status;

Approximate distinct counts in the index

APPROX_COUNT(col) maintains a HyperLogLog sketch per group, so a covered APPROX_COUNT returns a bounded-error distinct count with no scan. It is served byte-identically to the scan-path approximate function, so the answer does not change depending on whether the index served it.

CREATE INDEX idx_events_kind
  ON events (kind)
  USING PTI
  INCLUDE (COUNT(*), APPROX_COUNT(user_id));

SELECT kind, APPROX_COUNT(user_id) AS approx_users
FROM   events
GROUP BY kind;

For the full approximate surface (distinct counts, percentiles, histograms with explicit error bars), see Approximate Query Processing.

Filtering on the group column

A covered query may carry a WHERE as long as it filters only on the grouping column(s). Because every row in a group shares the group key, filtering groups by a group-key predicate is byte-identical to filtering rows — so it is served from the index. A WHERE that touches any other column falls back to a scan.

-- Covered: the WHERE is on the group column.
SELECT status, SUM(revenue)
FROM   orders
WHERE  status = 'COMPLETED'
GROUP BY status;

Physical B-Tree, Hash, and UNIQUE indexes

USING PTI, USING BTREE, or USING HASH without an INCLUDE clause builds a physical index structure. USING BTREE is the default when you omit USING.

CREATE INDEX idx_orders_customer ON orders (customer_id);              -- BTREE (default)
CREATE INDEX idx_orders_status_h ON orders (status) USING HASH;       -- hash-keyed
CREATE UNIQUE INDEX uq_users_email ON users (email);                  -- enforces uniqueness

Two runtime paths consume these structures today, and it’s worth being precise about what they do — and don’t — accelerate:

• UNIQUE enforcement — a CREATE UNIQUE INDEX verifies uniqueness over the existing rows at creation time (it fails, naming the offending key, if duplicates exist) and thereafter enforces it on INSERT / COPY with an index probe instead of a full-table scan.
• Sort-merge join eligibility — a single-column, order-preserving PTI or BTREE index whose column is a join key lets the planner run a sort-merge join instead of a hash join.

A plain CREATE INDEX does not add a general WHERE col = v point-lookup or range-scan fast-path — those queries still run their normal plan. Create these indexes for the two jobs above, not to speed up arbitrary filters.

Partial indexes

Append a WHERE predicate to index — and, for UNIQUE, enforce over — only the rows that match it.

-- Uniqueness enforced only among the active users.
CREATE UNIQUE INDEX uq_users_email_active
  ON users (email)
  WHERE status = 'active';

Managing indexes

Statement	What it does
DROP INDEX [IF EXISTS] <name>	Remove the index (catalog row + all runtime structures).
ALTER INDEX [IF EXISTS] <name> RENAME TO <new>	Metadata-only rename — re-keys the runtime registries with no scan or rebuild.
REINDEX [TABLE | INDEX] <name>	Rebuild the runtime structures for an index (or every index on a table) from current storage. The recovery verb for a stale or disabled structure; catalog metadata is untouched.

Index and constraint metadata is part of the catalog snapshot, so every index survives a restart: vector indexes rehydrate at boot, PTI/tree structures and covering aggregates rebuild from a scan, and declared graph indexes re-arm from the catalog.

Autonomous indexing

Beyond the indexes you declare, the engine watches query patterns and can create real physical indexes on its own when a missing-index pattern is repeatedly paying off. Autonomous indexing is throttled (it checks periodically and creates at most a couple of indexes per check), names its indexes eif_auto_*, only ever creates plain non-unique B-Tree indexes, and never touches system tables. It is gated by a configuration flag and, because the indexes are catalog-persisted, is self-healing — a workload that lost an auto-index simply causes it to be recreated. The explicitly-created indexes above remain the stable, predictable surface; treat the autonomous layer as an optimization that happens underneath you.

Key concepts

Term	What it means
Pre-computed aggregate	An exact SUM/COUNT/AVG/MIN/MAX, maintained per distinct value (or tuple) of the indexed column(s).
Pruning	The index proves whole regions of the table can’t match a query and skips them — no false positives, no missed rows.
Exact-or-scan	A covered read returns the exact pre-computed value or transparently falls back to a full scan — it never returns a stale or approximate value (except APPROX_COUNT, which is approximate by contract).
Freshness guarantee	A covering index serves only when it is provably current — any write to the table, through any path, is accounted for. Otherwise the query transparently runs a full scan; you never get a stale answer.

Notes & caveats

Tip

The bigger the INCLUDE list, the more queries can be answered without touching the table — but each extra aggregate costs maintenance work on writes. Start with the aggregates your dashboards actually run and add more as you see demand.

Always exact

Covered aggregates are maintained on the write path and a covered read is never served a stale value. If the engine can’t prove a covered answer is current and exact for a query — for example after an out-of-band bulk load, while a deleted MIN/MAX extreme is being recomputed, or beyond the group-cardinality cap — it transparently falls back to a full scan for that query. You get the same answer either way; only the speed differs.

UNIQUE on UPDATE

UNIQUE and partial-UNIQUE constraints are enforced on INSERT, COPY, and at CREATE INDEX time. An UPDATE that mutates a value into a collision is not currently re-checked against the uniqueness probe — a known boundary, not a regression.

Graph Index →

Serve selective relationship joins near-instantly, without scanning either table.

Vector Search (kNN) →

HNSW / IVFFLAT indexes over TENSOR columns.

Approximate Query Processing →

Sketches and digests behind APPROX_COUNT and percentiles.

Adaptive Query Execution →

How the optimizer and autonomous indexing adapt at runtime.

Catalog & Metadata →

Where index and constraint definitions live and how they recover.