SQL BenchMEASURED, NOT GUESSED

    ↑↓ to select, Enter to open, Esc to close

    GROUP BY score (indexed) is ~41.67× faster than GROUP BY payload (no index)

    `GROUP BY` on an indexed column is 42× faster: reading in index order means the aggregation needs no sort.

    Result

    RunGROUP BY score (indexed)GROUP BY payload (no index)Ratio
    Run 11.78874.5690.02×
    Run 21.66772.3820.02×
    Run 31.73072.1680.02×

    Spread over 3 runs: GROUP BY score (indexed) CV 2.9% / GROUP BY payload (no index) CV 1.5% / Ratio CV 2.0%

    GROUP BY score (indexed): 1.79msGROUP BY payload (no index): 74.57msRun1GROUP BY score (indexed): 1.67msGROUP BY payload (no index): 72.38msRun2GROUP BY score (indexed): 1.73msGROUP BY payload (no index): 72.17msRun3

    The statement

    SELECT score, COUNT(*) FROM items GROUP BY score LIMIT 100;
    SELECT payload, COUNT(*) FROM items GROUP BY payload LIMIT 100;

    Conditions

    100,000 rows; `payload` is identical in every row (so grouping produces one group).

    Where this number does not apply

    With many groups, or a `WHERE` on another column, the index is used differently.

    Reproduce

    cd bench && python sqbench.py --samples 9 --out ../data/run.json