为简洁起见,我简化了两个查询.
这是正常形式(大约需要20秒)：
SELECT *
FROM tableA
WHERE (columna = 1 OR columnb = 2) AND
atype = 35 AND
aid IN (1,2,3)
ORDER BY modified_at DESC
LIMIT 25;
以下是此查询的说明：http://explain.depesz.com/s/2v8
CTE表格(约120ms)：
WITH raw AS (
SELECT *
FROM tableA
WHERE (columna = 1 OR columnb = 2) AND
atype = 35 AND
aid IN (1,3)
)
SELECT *
FROM raw
ORDER BY modified_at DESC
LIMIT 25;
以下是CTE的解释：http://explain.depesz.com/s/uxy
只需将ORDER BY移动到查询的外部,就可以将成本降低99％.
我有两个问题：1)是否有一种方法可以构建第一个查询而不使用CTE,这样它在逻辑上等效性能更高,2)性能差异说明规划者如何确定如何获取数据？
关于上述问题,是否有其他统计信息或其他计划程序提示有助于提高第一个查询的性能？
编辑：取消限制还会导致查询使用堆扫描而不是向后索引扫描.如果没有LIMIT,查询将在40ms内完成.
在看到LIMIT的影响后,我尝试使用LIMIT 1,LIMIT 2等.当使用LIMIT 1和10s时,查询执行时间小于100毫秒. 1.
在考虑了这个之后,问题2归结为为什么规划器在一种情况下使用索引扫描而在另一种逻辑上等效的情况下使用位图堆扫描排序？在这两种情况下,我如何“帮助”规划者使用有效的计划？
更新：
我接受了克雷格的答案,因为它是最全面和最有帮助的.我最终解决问题的方法是使用一个实际上相同的查询,虽然在逻辑上不等同.问题的根源是在modified_at上的索引向后扫描索引.为了告知规划人员这不是一个好主意,我添加一个形式的谓词WHERE modified_at> = NOW() – INTERVAL’1 year’.这包括应用程序的足够数据,但阻止了规划人员沿着向后索引扫描路径向下移动.
Postgresql不会跨CTE边界进行优化.每个CTE子句都是独立运行的,其结果由查询的其他部分使用.所以像这样的查询：
WITH blah AS (
SELECT * FROM some_table
)
SELECT *
FROM blah
WHERE id = 4;
将导致完整的内部查询被执行. Postgresql不会将id = 4限定“推送”到内部查询中.在这方面,CTE是“优化围栏”,可以是好的也可以是坏的;它允许您在需要时覆盖计划程序,但如果确实需要下推,则会阻止您将CTE用作深度嵌套的FROM子查询链的简单语法清理.
如果你将上述内容改写为：
SELECT * FROM (SELECT * FROM some_table) AS blah WHERE id = 4;
使用FROM中的子查询而不是CTE,Pg会将质量下降到子查询中,并且它将全部运行良好且快速.
正如您所发现的,当查询计划程序做出糟糕的决定时,这也可以为您带来好处.在您的情况下,对于表的向后索引扫描看起来非常昂贵的是两个较小索引的位图或索引扫描,然后是过滤器和排序,但规划人员认为不会如此,因此它计划查询扫描指数.
当您使用CTE时,它无法将ORDER BY推送到内部查询中,因此您将覆盖其计划并强制它使用它认为的劣质执行计划 – 但事实证明它更好.
有一个讨厌的解决方法,可以用于这些称为OFFSET 0 hack的情况,但是你应该只使用它,如果你想办法让计划者做正确的事 – 如果你必须使用它,请将其归结为一个独立的测试用例,并将其作为可能的查询计划程序错误报告给Postgresql邮件列表.
相反,我建议首先考虑规划师为何做出错误的决定.
第一个候选人是统计/估计问题,当我们查看您的有问题的查询计划时,肯定会有3500个错误估计预期结果行的因素.这很大,但并不是不可能的大,尽管更有意思的是你实际上只得到一行计划器期待一个非平凡的行集.但这对我们没有多大帮助;如果行数低于预期,则意味着选择使用索引是比预期更好的选择.
主要问题似乎是它没有使用更小,更具选择性的索引sierra_kilo和papa_lima,因为它看到了ORDER BY并且认为它将节省更多时间进行向后索引扫描并避免排序而不是实际.鉴于只有一个匹配行可以排序,这是有道理的！如果它获得了预期的3500行,那么避免排序可能更有意义,尽管这仍然只是在内存中排序的相当小的行集.
你设置了enable_seqscan等参数吗？如果你这样做,请取消它们;它们仅用于测试,完全不适合生产使用.如果您没有使用enable_ params,我认为值得在Postgresql邮件列表pgsql-perform上提高它.然而,匿名计划使这有点困难,特别是因为没有保证一个计划中的标识符引用另一个计划中的相同对象,并且它们与您在查询中对该问题所写的内容不匹配.在邮件列表上询问之前,您需要生成一个正确的手工完成版本,其中所有内容都匹配.
您很有可能需要为任何人提供真正的价值来提供帮助.如果你不想在公共邮件列表上这样做,there’s another option available.(我应该注意,我根据我的个人资料为其中一个工作).
