How use parameters/variable and explain together

Question

I have my function with one parameter carAzimuth.

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

Inside I have some calculation and this query

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;

I want analyze the query performace. So I have to replace the variable for a constant. And works OK, and EXPLAIN PLAN show me is using the proper index.

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;

But is troublesome change each variable if want test different values. Then if you try

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;

Works but stop using the index and change for 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"

So is there a way I can create my variable and use it on the explain without having a function?

Im using the SQL editor on pgAdmin to run my queries.

Answer 1

Your assumption so WITH clause is equivalent function is wrong. Clause WITH is related to Common Table Expressions and has zero relations to functions. When you would to see plans from functions, there are two possibilities:

1. using a auto_explain extension - with this extension you can log any nested plans - just enable a option auto_explain.log_nested_statements

2. you can use a prepared statements - any SQL is translated transparently to prepared statement in 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                                                                      │
   └───────────────────────────────────────────────────────────────────────────────────────────────┘
   

   Attention: first 5 executions the plan is always fresh (is not reused, and it is generated again and again). After 5 execution, the plan can be generic and reused. See Plan Caching chapter in related documentation.