Re: Migration from DB2 to PostgreSQL

Hi,

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