Re: Migration from DB2 to PostgreSQL

Hi,

On Thu, Jun 20, 2013 at 10:27 AM, Chris Angelico <rosuav(at)gmail(dot)com> wrote:
> On Thu, Jun 20, 2013 at 11:10 AM, Amit Langote <amitlangote09(at)gmail(dot)com> wrote:
>> If this particular function is to be used repeatedly in a single
>> query, would the cost of having a wrapper function around the original
>> function be too large? For example, if this function appears in a
>> WHERE clause against a table containing millions of rows.
>
> 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

postgres=# create or replace function gt3(n int, m int, o int) returns
boolean as $$
begin
return gt(n, m);
end;
$$
language plpgsql;
CREATE FUNCTION
Time: 1.073 ms

postgres=# select count(*) from nums where gt3(num, 3450, 0);
count
--------
996550
(1 row)

Time: 2356.576 ms

postgres=# explain select count(*) from nums where gt3(num, 3450, 0);
QUERY PLAN
--------------------------------------------------------------------
Aggregate (cost=265258.34..265258.35 rows=1 width=0)
-> Seq Scan on nums (cost=0.00..264425.00 rows=333333 width=0)
Filter: gt3(num, 3450, 0)
(3 rows)

--
Amit Langote