Tom Lane wrote:
Rajesh Kumar Mallah <[EMAIL PROTECTED]> 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 ...
Sorry for the delayed response.
tradein_clients=# explain analyze SELECT company_id ,
data_bank.most_recent_edition(company_id) from public.branding_master
limit 50;
--------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.00..3.57 rows=50 width=4) (actual time=149.52..2179.49
rows=50 loops=1)
-> Seq Scan on branding_master (cost=0.00..6626.52 rows=92752
width=4) (actual time=149.51..2179.30 rows=51 loops=1)
tradein_clients=#
tradein_clients=#
tradein_clients=# explain analyze SELECT company_id ,
data_bank.most_recent_edition(company_id) from public.branding_master
limit 50;
DEBUG: StartTransactionCommand
LOG: plan:
{ LIMIT :startup_cost 0.00 :total_cost 185.65 :rows 1 :width 6
:qptargetlist
({ TARGETENTRY :resdom { RESDOM :resno 1 :restype 23 :restypmod -1
:resname
edition :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr
{ EXPR
:typeOid 23 :opType func :oper { FUNC :funcid 313 :funcresulttype 23
:funcretset false :funcformat 1 } :args ({ VAR :varno 1 :varattno 31
:vartype
21 :vartypmod -1 :varlevelsup 0 :varnoold 1 :varoattno 31})}} {
TARGETENTRY
:resdom { RESDOM :resno 2 :restype 23 :restypmod -1 :resname company_id
:reskey 0 :reskeyop 0 :ressortgroupref 1 :resjunk true } :expr { VAR
:varno 1
:varattno 1 :vartype 23 :vartypmod -1 :varlevelsup 0 :varnoold 1
:varoattno
1}}) :qpqual <> :lefttree { INDEXSCAN :startup_cost 0.00
:total_cost 24302.69
:rows 131 :width 6 :qptargetlist ({ TARGETENTRY :resdom { RESDOM :resno
1
:restype 23 :restypmod -1 :resname edition :reskey 0 :reskeyop 0
:ressortgroupref 0 :resjunk false } :expr { EXPR :typeOid 23 :opType
func
:oper { FUNC :funcid 313 :funcresulttype 23 :funcretset false
:funcformat 1 }
:args ({ VAR :varno 1 :varattno 31 :vartype 21 :vartypmod -1
:varlevelsup 0
:varnoold 1 :varoattno 31})}} { TARGETENTRY :resdom { RESDOM :resno 2
:restype
23 :restypmod -1 :resname company_id :reskey 0 :reskeyop 0
:ressortgroupref 1
:resjunk true } :expr { VAR :varno 1 :varattno 1 :vartype 23 :vartypmod
-1
:varlevelsup 0 :varnoold 1 :varoattno 1}}) :qpqual ({ EXPR :typeOid 16
:opType or :oper <> :args ({ EXPR :typeOid 16 :opType op :oper {
OPER :opno
96 :opid 65 :opresulttype 16 :opretset false } :args ({ VAR :varno 1
:varattno
19 :vartype 23 :vartypmod -1 :varlevelsup 0 :varnoold 1 :varoattno 19}
{
PARAM :paramkind 12 :paramid 1 :paramname \<unnamed> :paramtype
23 })} { EXPR
:typeOid 16 :opType op :oper { OPER :opno 96 :opid 65 :opresulttype 16
:opretset false } :args ({ VAR :varno 1 :varattno 1 :vartype 23
:vartypmod -1
:varlevelsup 0 :varnoold 1 :varoattno 1} { PARAM :paramkind 12 :paramid
1
:paramname \<unnamed> :paramtype 23 })})}) :lefttree <>
:righttree <> :extprm
() :locprm () :initplan <> :nprm 0 :scanrelid 1 :indxid (
310742439)
:indxqual (<>) :indxqualorig (<>) :indxorderdir -1 }
:righttree <> :extprm ()
:locprm () :initplan <> :nprm 0 :limitOffset <>
:limitCount { CONST
:consttype 23 :constlen 4 :constbyval true :constisnull false
:constvalue 4 [
1 0 0 0 ] }}
DEBUG: CommitTransactionCommand
But i feel it can be lot more faster , can anyone suggest me something
to try.
Create an index on old_company_id, perhaps.
Its there already..
branding_master_old_comapany_id btree (old_company_id),
regds , mallah.
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
|
