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.28 rows=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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers