why SPI_execute_with_args don't use known values for optimalisation

Hello

I would to use EXECUTE USING for optimalisation query

SELECT * FROM tab WHERE column = param OR param IS NULL

(if param isn't null then use param else ignore this params)

Planner can do it:

postgres=# explain select * from test where a = 100 or 100 is null;                            QUERY PLAN
---------------------------------------------------------------------Index Scan using test_idx on test
(cost=0.00..8.28rows=1 width=4)  Index Cond: (a = 100)
 
(2 rows)

postgres=# explain select * from test where a = null or null is null;                        QUERY PLAN
------------------------------------------------------------Seq Scan on test  (cost=0.00..1393.00 rows=100000 width=4)
(1 row)

but:

create or replace function test_using(p integer)
returns int as $$
declare r record;
begin for r in execute 'explain analyze select a from test where a = $1 or
$1 is null' using p loop   raise notice '%', r; end loop; return 0; end;
$$ language plpgsql;

postgres=# select test_using(100);
NOTICE:  ("Seq Scan on test  (cost=0.00..1643.00 rows=501 width=4)
(actual time=0.076..26.546 rows=1 loops=1)")
NOTICE:  ("  Filter: ((a = $1) OR ($1 IS NULL))")
NOTICE:  ("Total runtime: 26.596 ms")test_using
------------         0
(1 row)

Regards
Pavel Stehule