Rajesh Kumar Mallah <mallah@trade-india.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
