PostgreSQL SQL 函数比硬编码查询慢 10 倍

Question

如果我将以下函数硬编码到查询中，它的处理速度将提高 10 倍...关于如何使该函数运行得那么快有什么想法吗？

我认为编写 sql 函数的一个优点是查询计划器在这些函数上具有完整的功能，与 PL 语言函数相比。

顺便说一下，我使用的是 PostgreSQL 9.4。

---

更新

我现在意识到执行速度的差异不是来自将查询放在函数中，而是来自调用函数的方式。

select * from spatial.aggregate_raster_stats_by_geom(155); >> 1.5 秒

select (spatial.aggregate_raster_stats_by_geom(155)).*; >> 15 秒

---

CREATE OR REPLACE FUNCTION spatial.aggregate_raster_stats_by_geom(
  IN arg_rid INTEGER
) 
-- This function is called by the trigger that is fired whenever an entry is created in the raster catalog.
RETURNS TABLE(band INTEGER,gid INTEGER, rid INTEGER, product_id INTEGER,ref_datetime TIMESTAMP ,scale INTEGER, count BIGINT, sum FLOAT, mean FLOAT, stddev FLOAT, min FLOAT, max FLOAT) AS
$$
SELECT
    band,
    gid,
    arg_rid as rid,
    product_id,
    ref_datetime,
    scale,
    (ST_SummaryStats(clip,band,TRUE)).* -- compute summary statistics (min and max, etc are also in there). TRUE indicates that nodata should be ignored.
    FROM
      (SELECT
        gid,
        ST_Union(ST_Clip(rast, geom)) as clip -- assemble the raster tiles and clip them with the assembled polygons
      FROM 
        spatial.raster_tiles AS r
      JOIN 
        spatial.geom_catalog AS polygons
        ON ST_Intersects(rast,polygons.geom) -- only select raster tiles that touch the polygons. Spatial indexes should make this fast
      JOIN
        spatial.geom_metadata AS geometa
        ON geometa.product_id = polygons.product_id
      WHERE 
        geometa.aggregate_raster_auto = TRUE
        AND r.rid=$1

      GROUP by gid
      ) as foo
    cross join (
    -- Join bands to the selection
    -- this join has to be introduced AFTER the clipping. If done before, then clipping will be performed for each band.
        SELECT 
          generate_series(md.band_data_start,band_count) as band,
          data_scale_factor as scale,
          md.product_id,
          rid, 
          ref_datetime
        FROM spatial.raster_metadata md 
       JOIN spatial.raster_catalog rst ON rst.product_id = md.product_id
       WHERE rst.rid = $1) AS bar2
$$
  LANGUAGE sql IMMUTABLE;

Answer 1

啊。您遗漏了关键细节。

在 PostgreSQL 中(至少在 9.5 及更早版本中)写:

SELECT ( f () ).*;

... 为每个结果列运行一次 f!

它基本上是一个宏扩展。

要解决这个问题，请包裹在另一层子查询中。