questllens-tune-a-query

questllens-tune-a-query

The one question that fixes most slow queries

Does the query bound the designated timestamp? QuestDB partitions on it. Without a time predicate, every partition is read. With one, the engine touches only the overlapping partitions. This single change usually dwarfs every other optimization available to you.

describe_table with with_time_range=true tells you what range exists so your bound is real rather than guessed.

Order of operations

1. suggest_sample_by — before writing any time aggregate. Give it the table and, if known, from/to and a target_buckets. It returns the interval and the SQL. This exists because picking 1m over a year yields 525,600 buckets and a timeout.
2. explain_query — run it before query on anything nontrivial. It costs nothing and tells you whether QuestDB is pruning partitions or scanning everything.
3. query — only once the plan looks sane.

If query times out (QUERY_TIMEOUT_MS, default 30s), don't retry it unchanged. Narrow the time range, add a SYMBOL filter, or aggregate harder.

Reading the plan

explain_query output is where the answer usually is. Look for:

• Interval scan vs full table scan — an interval/partition-bounded scan means your time predicate is being used. A full scan on a partitioned table means it isn't. The usual cause is a predicate the engine can't push down: a function wrapped around the timestamp column (to_str(ts) = '...'), or a comparison against a non-constant expression. Compare the bare column to a literal.
• Row count estimates — wildly high estimates point at a missing filter, not a slow engine.
• Join strategy — for time-aligned joins, ASOF JOIN is the intended tool. A regular join plus a time-window predicate is both slower and usually subtly wrong.

QuestDB-specific rewrites that actually help

• Filter SYMBOLs before time, then bound time. SYMBOL comparison is an integer compare against the intern table; it's the cheapest predicate available. describe_table with with_symbol_stats=true shows cardinality — a high-cardinality SYMBOL filters much harder than a low-cardinality one.
• LATEST ON ... PARTITION BY for "most recent row per device". Do not emulate it with a window function or a self-join on max(ts); both read far more data.
• SAMPLE BY ... ALIGN TO CALENDAR when buckets must line up with wall-clock boundaries. Without ALIGN TO CALENDAR, buckets start at the first row's timestamp, which makes results shift as data arrives — a common source of "the numbers changed" confusion.
• Aggregate in SQL, never in the client. MAX_ROWS (default 1000) truncates results, so counting rows you pulled back gives a wrong answer silently. Use count().
• Check for an existing materialized view with get_mv_dependencies before optimizing an aggregate by hand. If an MV already computes it, query that instead.