SQLite查询规划器偏好行ID索引的问题及相关技术疑问

数据库结构 #

CREATE TABLE "posts" (
  "id" INTEGER PRIMARY KEY NOT NULL,
  "isDeleted" INTEGER,
  "isPublished" INTEGER,
  "publishTs" INTEGER,
  "updateTs" INTEGER,
  "title" TEXT,
  "contentJson" JSON
);

CREATE INDEX posts_defaultView ON "posts" (publishTs, updateTs)
WHERE
  isPublished IS NOT NULL
  AND isDeleted IS NULL;

CREATE VIRTUAL TABLE "_fts_posts" USING fts5("title", "contentPlain");

查询需求与原SQL语句 #

需求为筛选出匹配FTS查询且满足其他非FTS条件的数据，原查询语句如下：

SELECT
  *
FROM
  posts
  JOIN _fts_posts ON posts.rowId = _fts_posts.rowId
WHERE
  publishTs <= ?
  AND isPublished IS NOT NULL
  AND isDeleted IS NULL
  AND _fts_posts = ?
ORDER BY
  publishTs,
  updateTs
LIMIT
  10;

执行计划的问题 #

尽管存在覆盖非FTS过滤和排序条件的posts_defaultView索引，查询规划器仍选择了以下执行计划：

QUERY PLAN
|--SCAN _fts_posts VIRTUAL TABLE INDEX 0:M2
|--SEARCH posts USING INTEGER PRIMARY KEY (rowid=?)
`--USE TEMP B-TREE FOR ORDER BY

该策略在FTS过滤结果范围较广时性能极差，需要手动过滤数千条记录并排序。

手动修改后的矛盾结果 #

我尝试在JOIN子句中用+禁用行ID索引（JOIN _fts_posts ON +posts.rowId = _fts_posts.rowId），此时执行计划变为：

QUERY PLAN
|--SEARCH posts USING INDEX posts_defaultView (publishTs<?)
`--SCAN _fts_posts VIRTUAL TABLE INDEX 0:M2=

• 当FTS过滤范围广时，性能提升约30倍（真实数据库中因json_extract子句可达1000倍）。
• 但当FTS过滤范围窄时，该方案需要扫描整个posts_defaultView索引，性能比使用行ID索引差约500倍。

关于查询规划器行为的疑问 #

1. 如果第一步是SCAN _fts_posts VIRTUAL TABLE INDEX 0:M2，规划器应该能知晓FTS过滤结果的规模，以此评估行ID索引的实用性。当过滤范围覆盖80%的ID时，为何不选择更适配其余查询条件的索引？
2. 即便规划器无法估算行ID匹配概率，也可以利用rowId默认包含在索引中的特性。为何规划器选择使用行ID索引，手动过滤3个条件并排序2个字段，而非遍历已排序的索引仅过滤rowId？
3. 我尝试用likelihood函数解决问题1，但没有效果。将JOIN改为WHERE likelihood(rowId IN (SELECT rowId FROM _fts_posts WHERE _fts_posts = ?), 1.0)后，查询计划未改变，这是为什么？

内容的提问来源于stack exchange，提问作者Michał Piotr Dzienisiewicz