CTable Index Benchmark | N=1,000,000 REPS=5

Random data: sensor_id uniform random in [0, 100,000) Sorted data: sensor_id = 0,0,…,1,1,…,2,2,… (clustered, ~10 rows/value)

BUCKET

Stores min/max per chunk. Can skip chunks whose range doesn't overlap the query. Only effective when data is sorted/clustered. Useless on random data.

Range query — random data

──────────────────────────────────────────────────────────────────────
  Random data — BUCKET index
──────────────────────────────────────────────────────────────────────
  SELECTIVITY         ROWS   SCAN(ms)    IDX(ms)   SPEEDUP
  ────────────── ─────────  ─────────  ─────────  ────────
  0.1%                 922       12.8       10.1      1.3×
  1%                 9,879       13.7       14.7      0.9×
  5%                49,991       17.1       17.9      1.0×
  10%               99,775       19.8       21.0      0.9×
  25%              249,376       24.0       25.0      1.0×
  50%              499,826       24.0       27.2      0.9×  (slower)
  75%              749,665       23.2       27.5      0.8×  (slower)
──────────────────────────────────────────────────────────────────────

Range query — sorted data

──────────────────────────────────────────────────────────────────────
  Sorted data — BUCKET index
──────────────────────────────────────────────────────────────────────
  SELECTIVITY         ROWS   SCAN(ms)    IDX(ms)   SPEEDUP
  ────────────── ─────────  ─────────  ─────────  ────────
  0.1%                 990       11.9        2.5      4.8× ←
  1%                 9,990       11.9        2.2      5.5× ←
  5%                49,990       12.0        3.1      3.9× ←
  10%               99,990       12.1        5.1      2.4× ←
  25%              249,990       11.7        9.3      1.3×
  50%              499,990       12.3       19.0      0.6×  (slower)
  75%              749,990       11.9       35.9      0.3×  (slower)
──────────────────────────────────────────────────────────────────────

PARTIAL

Stores exact row positions. Works on any data layout. Smaller index than FULL; slightly less overhead to build.

Range query — random data

──────────────────────────────────────────────────────────────────────
  Random data — PARTIAL index
──────────────────────────────────────────────────────────────────────
  SELECTIVITY         ROWS   SCAN(ms)    IDX(ms)   SPEEDUP
  ────────────── ─────────  ─────────  ─────────  ────────
  0.1%                 922       12.4        1.9      6.4× ←
  1%                 9,879       14.4        2.5      5.8× ←
  5%                49,991       17.3        5.3      3.3× ←
  10%               99,775       20.1        8.8      2.3× ←
  25%              249,376       23.6       21.4      1.1×
  50%              499,826       26.2       46.4      0.6×  (slower)
  75%              749,665       22.8       75.2      0.3×  (slower)
──────────────────────────────────────────────────────────────────────

Range query — sorted data

──────────────────────────────────────────────────────────────────────
  Sorted data — PARTIAL index
──────────────────────────────────────────────────────────────────────
  SELECTIVITY         ROWS   SCAN(ms)    IDX(ms)   SPEEDUP
  ────────────── ─────────  ─────────  ─────────  ────────
  0.1%                 990       13.2        2.4      5.5× ←
  1%                 9,990       12.8        2.0      6.4× ←
  5%                49,990       12.5        2.6      4.9× ←
  10%               99,990       12.7        4.0      3.1× ←
  25%              249,990       12.0        8.1      1.5×
  50%              499,990       11.9       18.5      0.6×  (slower)
  75%              749,990       13.1       33.4      0.4×  (slower)
──────────────────────────────────────────────────────────────────────

Equality query — random data

  VALUE          ROWS   SCAN(ms)    IDX(ms)   SPEEDUP
  ──────────── ──────  ─────────  ─────────  ────────
  ==0              12       12.6        2.0      6.3× ←
  ==25,000         13       14.2        1.9      7.5× ←
  ==50,000          9       12.6        1.9      6.7× ←
  ==99,999          4       12.4        1.9      6.7× ←

Equality query — sorted data

  VALUE          ROWS   SCAN(ms)    IDX(ms)   SPEEDUP
  ──────────── ──────  ─────────  ─────────  ────────
  ==0              10       11.8        1.9      6.3× ←
  ==25,000         10       11.7        1.8      6.7× ←
  ==50,000         10       12.0        1.7      7.0× ←
  ==99,999         10       12.1        1.7      7.1× ←

FULL

Stores exact row positions with full chunk coverage. Best query performance; larger index than PARTIAL.

Range query — random data

──────────────────────────────────────────────────────────────────────
  Random data — FULL index
──────────────────────────────────────────────────────────────────────
  SELECTIVITY         ROWS   SCAN(ms)    IDX(ms)   SPEEDUP
  ────────────── ─────────  ─────────  ─────────  ────────
  0.1%                 922       13.2        2.1      6.4× ←
  1%                 9,879       15.3        2.8      5.5× ←
  5%                49,991       18.1        5.1      3.5× ←
  10%               99,775       20.5       11.0      1.9×
  25%              249,376       23.5       21.5      1.1×
  50%              499,826       25.4       46.1      0.6×  (slower)
  75%              749,665       23.2       86.9      0.3×  (slower)
──────────────────────────────────────────────────────────────────────

Range query — sorted data

──────────────────────────────────────────────────────────────────────
  Sorted data — FULL index
──────────────────────────────────────────────────────────────────────
  SELECTIVITY         ROWS   SCAN(ms)    IDX(ms)   SPEEDUP
  ────────────── ─────────  ─────────  ─────────  ────────
  0.1%                 990       12.0        1.9      6.4× ←
  1%                 9,990       12.0        2.0      6.1× ←
  5%                49,990       11.5        2.8      4.1× ←
  10%               99,990       12.0        4.2      2.9× ←
  25%              249,990       11.9        7.8      1.5×
  50%              499,990       11.8       18.5      0.6×  (slower)
  75%              749,990       11.5       44.5      0.3×  (slower)
──────────────────────────────────────────────────────────────────────

Equality query — random data

  VALUE          ROWS   SCAN(ms)    IDX(ms)   SPEEDUP
  ──────────── ──────  ─────────  ─────────  ────────
  ==0              12       12.1        2.5      4.8× ←
  ==25,000         13       12.0        2.0      6.1× ←
  ==50,000          9       12.4        2.0      6.2× ←
  ==99,999          4       12.6        2.0      6.4× ←

Equality query — sorted data

  VALUE          ROWS   SCAN(ms)    IDX(ms)   SPEEDUP
  ──────────── ──────  ─────────  ─────────  ────────
  ==0              10       11.7        1.8      6.5× ←
  ==25,000         10       11.5        1.7      6.6× ←
  ==50,000         10       12.4        1.7      7.1× ←
  ==99,999         10       12.3        1.8      7.0× ←

Cardinality comparison — sorted data, FULL index

Shows how repetition level affects speedup (data always sorted).

  CARDINALITY                   0.1% sel        1% sel        5% sel       10% sel
  ──────────────────────────────────────────────────────────────────────
  High rep  (10 uniq)              9.1×           9.6×           8.9×          10.1×
  Med  rep  (1k uniq)              8.5×           6.2×           4.3×           3.5×
  Low  rep  (1M uniq)              6.4×           5.9×           4.2×           3.2×
  ──────────────────────────────────────────────────────────────────────
  (speedup — higher is better)

Compound filter — sorted data, FULL index

sensor_id > X AND region == Y | region in [0,8) → ~12.5% per value

────────────────────────────────────────────────────────────────────────────────
  QUERY              ROWS     NO IDX    IDX:sid    IDX:reg      2 IDX  BEST
  ────────────── ────────  ─────────  ─────────  ─────────  ─────────  ────────────
  0.1%+12.5%          127      14.6ms       2.6ms      15.0ms      14.4ms  sid(5.6×)
  1%+12.5%          1,297      14.7ms       2.6ms      15.2ms      17.2ms  sid(5.7×)
  5%+12.5%          6,268      16.2ms       4.5ms      16.8ms      20.3ms  sid(3.6×)
  10%+12.5%        12,377      19.5ms       6.2ms      19.6ms      21.0ms  sid(3.2×)
────────────────────────────────────────────────────────────────────────────────

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Uh oh!

CTable Index Benchmark | N=1,000,000 REPS=5

BUCKET

Range query — random data

Range query — sorted data

PARTIAL

Range query — random data

Range query — sorted data

Equality query — random data

Equality query — sorted data

FULL

Range query — random data

Range query — sorted data

Equality query — random data

Equality query — sorted data

Cardinality comparison — sorted data, FULL index

Compound filter — sorted data, FULL index

Uh oh!

FilesExpand file tree

indexin.md

Latest commit

History

indexin.md

File metadata and controls

CTable Index Benchmark | N=1,000,000 REPS=5

BUCKET

Range query — random data

Range query — sorted data

PARTIAL

Range query — random data

Range query — sorted data

Equality query — random data

Equality query — sorted data

FULL

Range query — random data

Range query — sorted data

Equality query — random data

Equality query — sorted data

Cardinality comparison — sorted data, FULL index

Compound filter — sorted data, FULL index