Execution time variability with [] operator

To reproduce

I have this table

CREATE TABLE 'eq_equities_market_data' ( 
	timestamp TIMESTAMP_NS,
	symbol SYMBOL CAPACITY 256 CACHE,
	venue SYMBOL CAPACITY 32 CACHE,
	bids DOUBLE[][],
	asks DOUBLE[][]
) timestamp(timestamp) PARTITION BY HOUR WAL
;

I have ~560 million rows for 2025-11-25, of which ~18 millions are for the GOOGL symbol.

These type of queries have a lot of variability for the exec time:

SELECT      timestamp,
            symbol,
            venue,
            count(bids[1][1])
          FROM eq_equities_market_data          
          where symbol='AAPL' and timestamp in '2025-11-25'
sample by 1s;

SELECT      timestamp,
            symbol,
            venue,
            last(bids[1][1]) AS bid_price,
            last(bids[2][1]) AS bid_size,
            last(asks[1][1]) AS ask_price,
            last(asks[2][1]) AS ask_size
          FROM eq_equities_market_data          
          where symbol='AAPL' and timestamp in '2025-11-25'
sample by 1s;
--order by timestamp desc;


select timestamp,
       first(bids[1][1]) as open,
       max(bids[1][1])   as high,
       min(bids[1][1])   as low,
       last(bids[1][1])  as close,
       sum(bids[2][1])   as volume
from eq_equities_market_data
where symbol='AAPL' and timestamp in '2025-11-25'
sample by 15m
order by timestamp desc;

If we change the first query to

SELECT      timestamp,
            symbol,
            venue,
            count()
          FROM eq_equities_market_data          
          where symbol='AAPL' and timestamp in '2025-11-25'
sample by 1s;

It then executes much more stably.

To generate demo data, the python script at https://github.com/javier/equities-data-generator can be used.

To ingest 100 million events (skipping mat views, as those are not neeeded) you can run

python equities_data_generator.py \
  --host 172.31.42.41 \
  --pg_port 8812 \
  --user admin \
  --password quest \
  --token "REPLACE_ME_token" \
  --token_x "REPLACE_ME_token_x" \
  --token_y "REPLACE_ME_token_y" \
  --ilp_user REPLACE_USER \
  --protocol tcp \
  --mode faster-than-life \
  --processes 6 \
  --total_market_data_events 100_000_000 \
  --start_ts "2025-11-25T00:00:00.000000Z" \
  --end_ts   "2025-11-26T00:00:00.000000Z" \
  --market_data_min_eps 8200 \
  --market_data_max_eps 11000 \
  --trades_min_eps 100 \
  --trades_max_eps 200 \
  --min_levels 40 \
  --max_levels 40 \
  --create_views false \
  --short_ttl false \
  --incremental false \
  --prefix "eq_" \
  --yahoo_refresh_secs 300 \
  --session_pacing false \
  --offsession_trades full \
  --session_tz "America/New_York"

QuestDB version:

9.2

OS, in case of Docker specify Docker and the Host OS:

ubuntu

File System, in case of Docker specify Host File System:

ext4

Full Name:

javier ramirez

Affiliation:

questdb

Have you followed Linux, MacOs kernel configuration steps to increase Maximum open files and Maximum virtual memory areas limit?

• Yes, I have

Additional context

No response