Search Postgresql Archives

Re: Migration from DB2 to PostgreSQL

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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




[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux