我的表格文档约有 250 万行和 100 多列。我提供仅在查询列中使用:
CREATE TABLE document
(
id serial PRIMARY KEY,
organizationid INTEGER,
status_1 TEXT NOT NULL,
status_2 INTEGER DEFAULT 0 NOT NULL
);
CREATE INDEX ON document (organizationid);
CREATE INDEX ON document (status_1);
CREATE INDEX ON document (status_2);
需要优化下一个查询:
SELECT *
FROM document
WHERE status_1 = '42' AND status_2 = 0 AND organizationid = 42
ORDER BY id
LIMIT 25;
此查询对于其他组织非常有效,但组织 42 没有使用此过滤器的文档(或有时少于 20 个),并且它会产生错误的查询计划和缓慢的查询。问题在于表中值的分布。status_1 = '42' 的文档百分比为 95%,status_2 = 0 为 10%,organizationid = 42 为 2%。因此,DB 预计该过滤器约有 4750 个文档,并使用 documnet_pkey 进行索引扫描并扫描全表,但没有找到任何文档。这需要几分钟的时间。但是,如果我将组织更改为另一个包含大量文档的组织,则查询将花费不到一秒钟的时间。如果我将过滤器更改为包含少量文档的组织 - 数据库只需按组织 ID 使用索引,然后对结果文档进行排序。所需时间不到 50 毫秒。
如何加快组织 42 的查询速度?或者我需要研究什么?
我使用 PostgreSQL 12
启用自动真空和分析,并且我在测试之前再次运行分析。我已经将 GEQO_EFFORT 增加到 10(最大值),将 DEFAULT_STATISTICS_TARGET 增加到 1000(对其他查询产生更多价值影响),创建了统计信息:
CREATE STATISTICS custom_1 ON organizationid, status_1, status_2 FROM document;
创建特定索引:
CREATE INDEX ON document ((status_1 = '42' AND status_2 = 0 AND organizationid = 42));
并在所有更改后重新运行分析。但这没有帮助。我检查了 pg_stats 和 pg_stats_ext,它包含预期的正确统计信息。对于创建的统计数据,它在最常见的值中具有这些列的其他组合,并且没有此组合,因此可以假设其中的组合不常见。对于表达式上的索引,统计表明索引中只有假值。
这些查询的理想索引是:
这应该可以解决您遇到的主要问题的特定查询参数,并且还应该改进所有其他参数化。
当组合比预期更常见时,列上的自定义统计信息可能会很好地修复估计,但当组合比预期不那么常见时,通常无法很好地修复估计,这里就是这种情况。
您的表达式索引可以工作,但前提是查询写得很笨拙:
该表达式的自定义统计信息(而不是索引)也可以“工作”,但这些直到 v14 才实现,并且仍然需要编写笨拙的查询。但到那时,它将修复估计,但会抑制您想要让它使用的索引使用(单列索引上的位图),因此它会“工作”,但仍然无法工作。