防止PostgreSQL有时选择错误的查询计划

Question

防止PostgreSQL有时选择错误的查询计划

对于使用PostgreSQL 8.4.9进行查询的PostgreSQL性能，我遇到了一个奇怪的问题。此查询是在3D卷中选择一组点，使用LEFT OUTER JOIN若要添加存在关联ID的相关ID列，请执行以下操作。的小变化x范围会导致PostgreSQL选择不同的查询计划，执行时间从0.01秒到50秒不等。这是一个有问题的查询：

SELECT treenode.id AS id,
       treenode.parent_id AS parentid,
       (treenode.location).x AS x,
       (treenode.location).y AS y,
       (treenode.location).z AS z,
       treenode.confidence AS confidence,
       treenode.user_id AS user_id,
       treenode.radius AS radius,
       ((treenode.location).z - 50) AS z_diff,
       treenode_class_instance.class_instance_id AS skeleton_id  FROM treenode LEFT OUTER JOIN
         (treenode_class_instance INNER JOIN
          class_instance ON treenode_class_instance.class_instance_id   
= class_instance.id                            
AND class_instance.class_id = 7828307)
       ON (treenode_class_instance.treenode_id = treenode.id          
        AND treenode_class_instance.relation_id = 7828321)
    
  WHERE treenode.project_id = 4
    AND (treenode.location).x >= 8000
    AND (treenode.location).x <= (8000 + 4736)
    AND (treenode.location).y >= 22244
    AND (treenode.location).y <= (22244 + 3248)
    AND (treenode.location).z >= 0
    AND (treenode.location).z <= 100
  ORDER BY parentid DESC, id, z_diff
  LIMIT 400;

我不是解析这些查询计划的专家，但明显的区别似乎在于x它使用的范围是Hash Left Join为LEFT OUTER JOIN(这非常快)，而对于另一个范围，它使用的是Nested Loop Left Join(这似乎很慢)。在这两种情况下，查询都返回大约90行。如果我做了SET ENABLE_NESTLOOP TO FALSE在查询的慢版本之前，它运行得非常快，但我理解一般来说，使用这种设置是个坏主意。.

例如，我是否可以创建一个特定的索引，以使查询计划者更有可能选择明显更有效的策略？有人能提出为什么PostgreSQL的查询规划器会为其中一个查询选择这么糟糕的策略吗？下面我已经包含了可能有帮助的模式的细节。

Answer 1

如果查询规划者做了错误的决定，这主要是两件事中的一件：1.统计是不准确的。你跑吗？ANALYZE足够的?在它的组合形式中也很流行VACUUM ANALYZE..如果自真空是ON(这是现代Postgres的默认设置)，ANALYZE自动运行。但请考虑：是否仍建议在9.1以下进行常规真空分析？(前两个答案仍然适用于Postgres 9.6。)如果你的桌子是大的而数据分布是不规则，提高default_statistics_target也许会有帮助。或者说，只是设置统计目标相关栏(WHERE或JOIN(基本上是查询的子句)：ALTER TABLE ... ALTER COLUMN ... SET STATISTICS 400;  -- calibrate number目标可以设定在0到10000之间；跑ANALYZE在此之后(在相关的表格上)。2.成本设置对规划师来说，估计值已经取消了。读这一章计划成本常数在手册里。看看这些章节默认统计量目标和随机页成本在这个问题上一般有用的PostgreSQL Wiki页面.还有许多其他可能的原因，但这些是目前为止最常见的原因。

Answer 2

欧文对统计数字的评价。此外：ORDER BY parentid DESC, id, z_diff分拣parentid DESC, id, z可能会给乐观主义者更多的洗牌空间。(我认为这不会有多大关系，因为这是最后一个学期，而且也不那么昂贵，但你可以试一试)