时间戳

首页 > 解决方案 > PostgreSQL 函数运行速度比直接查询慢得多

postgresql - PostgreSQL 函数运行速度比直接查询慢得多

问题描述

当我试图将一个简单的 sql 语句包装为一个函数时，我的问题就开始了。需要几毫秒才能完成的请求开始需要几秒钟。我在谷歌上阅读了所有我能读到的东西，但都是关于使用函数参数的。然后我摆脱了参数，但这并没有解决问题。

所以，现在我有以下 SQL 语句

select fn_tz(idata.system_timestamp), gp.zname, fc.*
from jv idata   
join gp on st_distance(waypoint,geom)=0
join fc ON 
    idata.fare_code = fc.fare_code  AND 
    fc.validity @> fn_tz(idata.system_timestamp)::date  and 
    fc.polygon_name = gp.name
where idata.item_id='21159704983720122917';

当我直接运行此查询时，执行大约需要 80 毫秒。但是，如果我在函数体中不做任何修改地输入相同的查询，则大约需要 10 秒！

无论如何，我已经尝试了所有想到的东西

  1. 常规 PLPGSQL 函数 ( return query ...)
  2. 常规 SQL 函数 ( select ...)
  3. 带有动态 SQL 查询的 PLPGSQL 函数 ( return query execute 'select ...')
  4. 终于我尝试了准备/执行语句

所有上述方法都给出相同的结果 - 10 秒。

然后我运行EXPLAIN ANALYZE EXECUTE ...准备好的语句，但即使从它的输出我也无法理解为什么它运行 10 秒

Hash Join  (cost=75.05..962862.24 rows=110 width=8) (actual time=1.075..10290.527 rows=476091 loops=1)
  Hash Cond: ((idata.fare_code = fc.fare_code) AND (gp.name = (fc.polygon_name)::text))
  Join Filter: (fc.validity @> (fn_tz(idata.system_timestamp))::date)
  ->  Nested Loop  (cost=0.00..925098.69 rows=59399 width=54) (actual time=0.298..8300.070 rows=53922 loops=1)
        Join Filter: (st_distance(idata.waypoint, gp.geom) = '0'::double precision)
        Rows Removed by Join Filter: 2212398
        ->  Seq Scan on jv idata  (cost=0.00..4402.99 rows=53999 width=54) (actual time=0.039..33.038 rows=53960 loops=1)
              Filter: (item_id = '21159704983720122917'::text)
              Rows Removed by Filter: 3079
        ->  Materialize  (cost=0.00..13.30 rows=220 width=64) (actual time=0.000..0.003 rows=42 loops=53960)
              ->  Seq Scan on gp  (cost=0.00..12.20 rows=220 width=64) (actual time=0.006..0.025 rows=42 loops=1)
  ->  Hash  (cost=40.22..40.22 rows=2322 width=16) (actual time=0.717..0.717 rows=2268 loops=1)
        Buckets: 4096  Batches: 1  Memory Usage: 141kB
        ->  Seq Scan on fc  (cost=0.00..40.22 rows=2322 width=16) (actual time=0.008..0.332 rows=2322 loops=1)
Planning Time: 0.008 ms
Execution Time: 10324.558 ms

令人惊讶的是，如果我EXPLAIN ANALYZE在原始查询上运行，它也需要大约 10 秒并生成几乎相同的执行计划。

我的服务器 - Google Cloud Platform 上 Postgres 11.8 的托管实例

我还能做什么/尝试？

Upd： 看来我需要强调一下——我不是在寻找提高查询性能的方法。直接查询运行 80 毫秒，我对此很满意。我想找到原因 - 为什么直接查询比函数体快100 次（！） 。

标签： postgresqlpostgresql-performance

解决方案

fn_tz可能会消耗大约 2 秒的性能，但主要问题是您使用的是st_distance而不是st_dwithin，它可以通过索引来支持：

select fn_tz(idata.system_timestamp), gp.zname, fc.*
from jv idata   
join gp on st_dwithin(waypoint, geom, 0)
join fc ON 
    idata.fare_code = fc.fare_code  AND 
    fc.validity @> fn_tz(idata.system_timestamp)::date  and 
    fc.polygon_name = gp.name
where idata.item_id='21159704983720122917';

该指数将是：

CREATE INDEX ON jv USING gist (waypoint);

推荐阅读