On Thu, Jun 20, 2013 at 11:35 AM, Amit Langote <amitlangote09@xxxxxxxxx> wrote: > On Thu, Jun 20, 2013 at 10:27 AM, Chris Angelico <rosuav@xxxxxxxxx> wrote: >> If your wrapper function is written in SQL and is trivial (eg ignore >> the third parameter and pass the other two on), the planner should be >> able to optimize right through it. Best way to find out is with >> EXPLAIN, which I've been using a good bit lately. The optimizer's >> pretty smart. > > For example consider following rough example: > > postgres=# create table nums as select * from generate_series(1,1000000) as num; > SELECT > Time: 1185.589 ms > postgres=# select count(*) from nums where num > 3450; > count > -------- > 996550 > (1 row) > > Time: 183.987 ms > > postgres=# create or replace function gt(n int, m int) returns boolean as $$ > begin > return n > m; > end; > $$ > language plpgsql; > CREATE FUNCTION > Time: 1.080 ms > > postgres=# select count(*) from nums where gt(num, 3450); > count > -------- > 996550 > (1 row) > > Time: 1327.800 ms > Huge difference between 'language plpgsql' and 'language sql'. Here's my timings using your code - similar to your timings: rosuav=> select count(*) from nums where num > 3450; count -------- 996550 (1 row) Time: 293.836 ms rosuav=> select count(*) from nums where gt(num, 3450); count -------- 996550 (1 row) Time: 2412.186 ms rosuav=> select count(*) from nums where gt3(num, 3450, 0); count -------- 996550 (1 row) Time: 4332.554 ms Now here's the SQL version of the code: rosuav=> create or replace function gt(n int, m int) returns boolean as 'select n>m' language sql; CREATE FUNCTION Time: 39.196 ms rosuav=> select count(*) from nums where gt(num, 3450); count -------- 996550 (1 row) Time: 258.153 ms rosuav=> create or replace function gt3(n int, m int,o int) returns boolean as 'select gt(n,m)' language sql; CREATE FUNCTION Time: 21.891 ms rosuav=> select count(*) from nums where gt3(num, 3450, 0); count -------- 996550 (1 row) Time: 258.998 ms The original, the one with the SQL function, and the one with two SQL functions, all are within margin of error. (Repeated execution shows times varying down as far as 237ms for the last one.) ChrisA -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
