sql - 如何使用参数/变量并一起解释

Question

我的函数有一个参数 carAzimuth。

CREATE OR REPLACE FUNCTION map.get_near_link(x numeric, y numeric, carAzimuth numeric)

里面我有一些计算和这个查询

SELECT *
FROM map.vzla_seg S
WHERE
    S.azimuth > carAzimuth - 30  and   
    S.azimuth < carAzimuth + 30  or
    S.azimuth < carAzimuth - 330 or 
    S.azimuth > carAzimuth + 330;

我想分析查询性能。所以我必须将变量替换为常量。工作正常，EXPLAIN PLAN 显示我正在使用正确的索引。

EXPLAIN ANALYZE    
 SELECT *
 FROM map.vzla_seg S
 WHERE
    S.azimuth > 345 - 30  and   
    S.azimuth < 345 + 30  or
    S.azimuth < 345 - 330 or 
    S.azimuth > 345 + 330;

但是如果想测试不同的值，改变每个变量是很麻烦的。那么如果你尝试

EXPLAIN ANALYZE  
  WITH param(carAzimuth) as (select 345) 
  SELECT *
  FROM vzla_seg S, param 
  WHERE
      S.azimuth > carAzimuth- 30 and   
      S.azimuth < carAzimuth + 30 or
      S.azimuth < carAzimuth - 330 or 
      S.azimuth > carAzimuth + 330;

工作但停止使用索引并更改为 FULL SCAN

"Nested Loop  (cost=0.01..208990.91 rows=2328905 width=4) (actual time=0.146..4138.882 rows=642115 loops=1)"
"  Join Filter: (((s.azimuth > (p.carazimuth - 30)) AND (s.azimuth < (p.carazimuth + 30))) OR (s.azimuth < (p.carazimuth - 330)) OR (s.azimuth > (p.carazimuth + 330)))"
"  Rows Removed by Join Filter: 3207719"
"  CTE parameter"
"    ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.002..0.002 rows=1 loops=1)"
"  ->  CTE Scan on parameter p  (cost=0.00..0.02 rows=1 width=4) (actual time=0.009..0.012 rows=1 loops=1)"
"  ->  Seq Scan on vzla_seg s  (cost=0.00..93496.22 rows=3849822 width=4) (actual time=0.072..1380.356 rows=3849834 loops=1)"
"Total runtime: 4253.113 ms"

那么有没有一种方法可以创建我的变量并在没有函数的情况下在解释中使用它？

我使用 pgAdmin 上的 SQL 编辑器来运行我的查询。

Answer 1

您假设 WITH 子句是等效函数是错误的。子句 WITH 与 Common Table Expressions 有关与函数的关系为零。当您希望从功能中查看计划时，有两种可能性:

1. 使用 auto_explain扩展 - 使用此扩展，您可以记录任何嵌套计划 - 只需启用一个选项 auto_explain.log_nested_statements

2. 您可以使用准备好的语句 - 任何 SQL 都会透明地转换为 PLpgSQL 中的准备好的语句。

   prepare xx(int) as select * from foo where a = $1::text;
   explain analyze execute xx(10);
   ┌───────────────────────────────────────────────────────────────────────────────────────────────┐
   │                                          QUERY PLAN                                           │
   ╞═══════════════════════════════════════════════════════════════════════════════════════════════╡
   │ Seq Scan on foo  (cost=0.00..21.00 rows=4 width=64) (actual time=0.019..0.019 rows=0 loops=1) │
   │   Filter: (a = '10'::text)                                                                   │
   │   Rows Removed by Filter: 2                                                                   │
   │ Execution time: 0.052 ms                                                                      │
   └───────────────────────────────────────────────────────────────────────────────────────────────┘
   

   注意:前 5 次执行计划总是新鲜的(不会重复使用，它会一次又一次地生成)。 5次执行后，计划就可以通用，复用了。请参阅 related documentation 中的计划缓存章节.