Re: Why performance improvement on converting subselect to a function ?

Rajesh Kumar Mallah <mallah(at)trade-india(dot)com> writes:
> explain analyze SELECT company_id , (SELECT edition FROM ONLY
> public.branding_master b WHERE old_company_id = a.company_id OR company_id =
> a.company_id ORDER BY b.company_id DESC LIMIT 1) from public.branding_master
> a limit 50;
> Total runtime: 19429.76 msec

> CREATE FUNCTION most_recent_edition (integer) returns integer AS 'SELECT
> edition::integer FROM ONLY public.branding_master b WHERE old_company_id = $1
> OR company_id = $1 ORDER BY b.company_id DESC LIMIT 1 ' language 'sql';

> tradein_clients=# explain analyze SELECT company_id ,
> most_recent_edition(company_id) from public.branding_master limit 50;
> Total runtime: 3969.52 msec

Odd. Apparently the planner is picking a better plan in the function
context than in the subselect context --- which is strange since it
ought to have less information.

AFAIK the only way to see the plan generated for a SQL function's query
is like this:

regression=# create function foo(int) returns int as
regression-# 'select unique1 from tenk1 where unique1 = $1' language sql;
CREATE FUNCTION
regression=# set debug_print_plan TO 1;
SET
regression=# set client_min_messages TO debug;
SET
regression=# select foo(55);
DEBUG: plan:
DETAIL: {RESULT :startup_cost 0.00 :total_cost 0.01 :plan_rows 1 :plan_width 0
:targetlist ({TARGETENTRY :resdom {RESDOM :resno 1 :restype 23 :restypmod -1
:resname foo :ressortgroupref 0 :resorigtbl 0 :resorigcol 0 :resjunk false}
:expr {FUNCEXPR :funcid 706101 :funcresulttype 23 :funcretset false
... (etc etc)

Would you do that and send it along? I'm curious ...

> But i feel it can be lot more faster , can anyone suggest me something
> to try.

Create an index on old_company_id, perhaps.

regards, tom lane