On Thu, Jun 20, 2013 at 10:54 AM, Chris Angelico <rosuav@xxxxxxxxx> wrote: > 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: > Umm, my bad! I almost forgot I could write pure SQL function bodies. Although, why does following happen? (sorry, a 8.4.2 installation) : postgres=# create or replace function gt(n int, m int) returns boolean as 'select n>m' language sql; ERROR: column "n" does not exist LINE 2: as 'select n>m' language sql; -- Amit Langote -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
