如何提升QuestDB查询性能？索引与WITH关键字使用疑问

一、当前配置的核心问题 #

• 内存资源不足：4GB内存对应120GB存储数据，QuestDB作为列式数据库需要足够内存缓存热数据、处理查询中间结果，默认内存分配会导致频繁磁盘IO，拖慢查询速度。
• 线程配置未匹配CPU：默认共享工作线程数未充分利用4核CPU的并行能力，无法同时处理查询的多阶段任务。
• 查询语句低效：原查询使用SELECT *加载所有列，JOIN前未做充分过滤，导致中间数据集过大，内存开销激增。

二、提升读取性能的具体方案 #

1. 调整系统配置参数 #

• 内存分配：修改server.conf中的query.memory.available，建议设置为2.5GB左右（占总内存的60%-70%），确保查询有足够内存缓存数据和处理JOIN。
• 线程数设置：将shared.worker.count设置为4或8（CPU核心数的1-2倍），让查询能并行处理数据扫描和JOIN操作。

2. 索引优化：为trigger_id创建哈希索引 #

必须创建，因为JOIN依赖trigger_id的等值匹配，哈希索引能将JOIN的时间复杂度从O(n)降低到O(1)左右，大幅加速查询。执行以下命令：

CREATE INDEX trigger_id_idx ON foo(trigger_id);
CREATE INDEX trigger_id_idx ON bar(trigger_id);

QuestDB的哈希索引对字符串类型的等值查询优化效果显著，且不会明显影响Influx Line Protocol的写入性能（写入时的索引维护开销可控）。

3. 查询语句优化：替代WITH子句的高效写法 #

原WITH子句会先执行全量JOIN再过滤，导致中间数据集过大，推荐两种替代方案：

方案1：合并过滤条件到主查询

直接将所有过滤条件前置，让查询优化器先筛选出小数据集再执行JOIN：

SELECT count_distinct(foo.trigger_id)
FROM foo
INNER JOIN bar 
  ON foo.trigger_id = bar.trigger_id
WHERE bar.some_internal_id = 24
  AND bar.timestamp BETWEEN '2023-08-01T00:00:00.000Z' AND '2023-08-01T23:59:59.999Z'
  AND foo.another_internal_id = 777666
  AND some_field = 1
  AND another_internal_id IN (100, 150);

方案2：先过滤再JOIN（更高效）

先分别过滤两个表的数据集，再执行JOIN，减少参与JOIN的数据量：

SELECT count_distinct(f.trigger_id)
FROM (
  SELECT trigger_id, some_field, another_internal_id
  FROM foo
  WHERE another_internal_id = 777666
    AND some_field = 1
    AND another_internal_id IN (100, 150)
) f
INNER JOIN (
  SELECT trigger_id
  FROM bar
  WHERE some_internal_id = 24
    AND timestamp BETWEEN '2023-08-01T00:00:00.000Z' AND '2023-08-01T23:59:59.999Z'
) b ON f.trigger_id = b.trigger_id;

同时必须去掉SELECT *，只查询需要的列，避免加载冗余数据占用内存和IO资源。

4. 写入页大小配置的作用 #

修改writer.page.size对读取性能几乎没有直接影响，该参数主要控制写入时的磁盘块批量写入大小。当前写入性能良好，无需调整这个参数，优先关注内存、线程和查询优化。

三、总结优化优先级 #

1. 重构查询语句，去掉SELECT *并前置过滤条件；
2. 为trigger_id创建哈希索引；
3. 调整内存和工作线程配置；
4. 无需修改写入页大小。

内容的提问来源于stack exchange，提问作者Andrey