重用计算出的选择值

小编典典

重用计算出的选择值

sql

我正在尝试使用ST_SnapToGrid，然后GROUP BY使用网格单元格（x，y）。这是我首先要做的：

SELECT
  COUNT(*)                      AS n,
  ST_X(ST_SnapToGrid(geom, 50)) AS x,
  ST_Y(ST_SnapToGrid(geom, 50)) AS y
FROM points
GROUP BY x, y

我不想ST_SnapToGrid为x和重新计算y。所以我将其更改为使用子查询：

SELECT
  COUNT(*)   AS n,
  ST_X(geom) AS x,
  ST_Y(geom) AS y
FROM (
  SELECT 
      ST_SnapToGrid(geom, 50) AS geom
  FROM points
) AS tmp
GROUP BY x, y

但是，当我运行时EXPLAIN，这两个查询都具有完全相同的执行计划：

GroupAggregate  (...)
  ->  Sort  (...)
        Sort Key: (st_x(st_snaptogrid(points.geom, 0::double precision))), (st_y(st_snaptogrid(points.geom, 0::double precision)))
        ->  Seq Scan on points  (...)

问题：PostgreSQL会重用的结果值ST_SnapToGrid()吗？
如果没有，是否有办法做到这一点？

阅读 141

2021-04-07

共1个答案

小编典典

测试时间

您不会在EXPLAIN输出中看到每行各个功能的评估。

测试EXPLAIN ANALYZE以获取实际查询时间以比较总体效果。运行几次以排除缓存工件。对于像这样的简单查询，可以通过以下方式获得整个运行时的更可靠的数字：

EXPLAIN (ANALYZE, TIMING OFF) SELECT ...

需要Postgres 9.2+
。每个文档：

TIMING

在输出中包括实际的启动时间和在每个节点上花费的时间。重复读取系统时钟的开销可能会在某些系统上显着降低查询速度，因此将此参数设置为FALSE仅需要实际行计数而不是确切时间时可能会很有用。即使使用此选项关闭了节点级计时，也始终会测量整个语句的运行时间。仅当ANALYZE同时启用此参数时，才可以使用此参数。默认为TRUE。

防止重复评估

通常，子查询中的表达式只计算一次。但是，如果Postgres认为这样做会更快，那么它可以使琐碎的子查询崩溃。

要引入优化障碍，可以使用
CTE
代替子查询。这保证了PostgresST_SnapToGrid(geom, 50)仅计算一次：

WITH cte AS (
   SELECT ST_SnapToGrid(geom, 50) AS geom1
   FROM   points
   )
SELECT COUNT(*)   AS n
     , ST_X(geom1) AS x
     , ST_Y(geom1) AS y
FROM   cte
GROUP  BY geom1;         -- see below

但是，由于CTE的开销较大，因此它可能比子查询慢
。函数调用可能非常便宜。通常，Postgres会更好地了解如何优化查询计划。如果您更了解，请仅引入这样的优化障碍。

简化

我在子查询/ CTE中更改了计算点的名称，geom1以阐明它与原始查询点的不同geom。这有助于在这里澄清 更重要的 事情：

GROUP BY geom1

代替：

~~GROUP BY x, y~~

这显然更便宜-可能会影响函数调用是否重复。因此，这可能是最快的：

SELECT COUNT(*) AS n
     , ST_X(ST_SnapToGrid(geom, 50)) AS x
     , ST_y(ST_SnapToGrid(geom, 50)) AS y
FROM   points
GROUP  BY ST_SnapToGrid(geom, 50);         -- same here!

也许这样：

SELECT COUNT(*)    AS n
     , ST_X(geom1) AS x
     , ST_y(geom1) AS y
FROM (
   SELECT ST_SnapToGrid(geom, 50) AS geom1
   FROM   points
   ) AS tmp
GROUP  BY geom1;

用EXPLAIN ANALYZE或测试这三个，EXPLAIN (ANALYZE, TIMING OFF)然后自己看看。测试>>猜测。

2021-04-07