Why the Same HDB Query Can Run at Very Different Speeds

Question

The exact same query against an HDB partition sometimes runs close to memory speed, and sometimes runs noticeably slower — without any change to the query or the data.

What causes the difference?

Solution

The difference is usually whether the pages required by the query are already present in the operating system's page cache ("warm") or must first be fetched from disk ("cold").

For example:

\t select avg price from trades
   where date=2024.01.02,
         sym=`AAPL

\t select avg price from trades
   where date=2024.01.02,
         sym=`AAPL

The second execution is often faster than the first because the pages needed by the query were already loaded into the OS page cache during the first run.

Explanation

Memory-Mapped Does Not Mean Memory-Resident

A common misconception is:

Memory Mapped
       =
Already In RAM

This is not true.

Memory mapping simply establishes a relationship between:

Virtual Address
        ↕
File Offset

The file may be fully mapped while none of its pages are currently resident in physical memory.

Whether data is actually in RAM depends on the operating system's page cache.

Cold Query

Suppose the required pages are not currently cached.

The query flow looks something like:

Query
  ↓
Page Fault
  ↓
Disk Read
  ↓
OS Page Cache
  ↓
Result

When the query touches a page that is not already in memory, the operating system must:

Locate the page on disk
Read it into RAM
Update the page cache
Resume execution

This additional disk I/O increases latency.

Warm Query

If the same query is executed again shortly afterwards:

Query
  ↓
OS Page Cache
  ↓
Result

The operating system already has the required pages in RAM.

No disk access is required, so the query can complete much more quickly.

Why Performance Can Vary So Much

The difference between a warm and cold query depends on several factors:

Storage type (NVMe, SSD, network storage, spinning disk)
Amount of data being accessed
Number of pages already cached
Overall system memory pressure

The performance difference may be:

200ms → 250ms

for one query, but:

5ms → 500ms

for another.

There is no fixed multiplier.

The effect depends entirely on how much disk I/O is avoided by the page cache.

Why Repeated Queries Often Get Faster

Consider:

\t select max price
   from trades
   where date=2024.01.02

\t select max price
   from trades
   where date=2024.01.02

The first query may need to load pages from disk:

Disk
  ↓
Page Cache
  ↓
Query

The second query often reuses those same cached pages:

Page Cache
  ↓
Query

This is why benchmarking HDB queries using a single execution can be misleading.

Many production systems naturally warm their working set throughout the trading day.

The Page Cache Is Shared

An important property of the OS page cache is that it is shared across processes.

Process A
                 │
                 ▼
           OS Page Cache
                 ▲
                 │
             Process B

If one q process reads a page from disk, another q process mapping the same file can often reuse the already-cached page.

This is one of the major benefits of memory mapping.

Unlike ordinary heap allocations, the cached data is not duplicated per process.

Why This Matters for HDBs

Most HDB queries access only a subset of:

partitions
columns
rows

For example:

select avg price
  by sym
  from trades
  where date=2024.01.02,
      sym in `AAPL`MSFT

Only the pages required for the relevant columns and rows are touched.

If those pages are already cached:

RAM Speed

If not:

Disk Speed

This is often the single biggest reason identical HDB queries can exhibit dramatically different runtimes.

Key Takeaway

Memory mapping does not guarantee that data is already in RAM.

The performance of an HDB query is heavily influenced by whether the pages it needs are already present in the operating system's page cache.

Cold Query
  ↓
Disk Read Required
  ↓
Slower

Warm Query
  ↓
Data Already Cached
  ↓
Faster

As a result, the exact same HDB query can produce very different runtimes depending solely on the state of the OS page cache.