X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.130]) by svr1.postgresql.org (Postfix) with ESMTP id C4346D1C4EC for ; Tue, 29 Jul 2003 16:47:03 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new, port 10024) with ESMTP id 81378-07 for ; Tue, 29 Jul 2003 13:46:00 -0300 (ADT) Received: from trade-india.com (ns5.trade-india.com [66.234.10.13]) by svr1.postgresql.org (Postfix) with SMTP id A926BD1C4CB for ; Tue, 29 Jul 2003 13:46:57 -0300 (ADT) Received: (qmail 27063 invoked by uid 0); 29 Jul 2003 16:46:23 -0000 Received: from mallah@trade-india.com by ns5.trade-india.com by uid 502 with qmail-scanner-1.15 (uvscan: v4.1.60/v4171. Clear:. Processed in 2.144034 secs); 29 Jul 2003 16:46:23 -0000 Received: from unknown (HELO trade-india.com) (203.145.130.142) by ns5.trade-india.com with SMTP; 29 Jul 2003 16:46:20 -0000 Message-ID: <3F26A503.3050104@trade-india.com> Date: Tue, 29 Jul 2003 22:16:59 +0530 From: Rajesh Kumar Mallah User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.4) Gecko/20030701 X-Accept-Language: en-us, en MIME-Version: 1.0 To: Tom Lane Cc: pgsql-performance@postgresql.org Subject: Re: Why performance improvement on converting subselect References: <200307291114.29567.mallah@trade-india.com> <21657.1059487755@sss.pgh.pa.us> In-Reply-To: <21657.1059487755@sss.pgh.pa.us> Content-Type: multipart/alternative; boundary="------------080809040000060809080803" X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200307/468 X-Sequence-Number: 2761 This is a multi-part message in MIME format. --------------080809040000060809080803 Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit Tom Lane wrote: >Rajesh Kumar Mallah 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 \ :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 \ :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 majordomo@postgresql.org) > > --------------080809040000060809080803 Content-Type: text/html; charset=us-ascii Content-Transfer-Encoding: 7bit Tom Lane wrote:
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 ...

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 majordomo@postgresql.org)
  

--------------080809040000060809080803--