Re: Migration from DB2 to PostgreSQL

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

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