On Thu, Jun 20, 2013 at 11:35 AM, Amit Langote <amitlangote09@gmail.com> wrote:
> On Thu, Jun 20, 2013 at 10:27 AM, Chris Angelico <rosuav@gmail.com> 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