pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Rajesh Kumar Mallah <mallah@trade-india.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-performance@postgresql.org
Subject: Re: Why performance improvement on converting subselect
Date: Tue, 29 Jul 2003 22:16:59 +0530
Message-ID: <3F26A503.3050104@trade-india.com> (raw)
In-Reply-To: <21657.1059487755@sss.pgh.pa.us>
References: <200307291114.29567.mallah@trade-india.com>
	<21657.1059487755@sss.pgh.pa.us>

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)
>  
>

view thread (9+ messages)  latest in thread

Message-ID: <3F26A503.3050104@trade-india.com>
Permalink:  ../3F26A503.3050104@trade-india.com/
Also on:    postgresql.org/message-id/3F26A503.3050104@trade-india.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-performance@postgresql.org
  Cc: mallah@trade-india.com, tgl@sss.pgh.pa.us
  Subject: Re: Why performance improvement on converting subselect
  In-Reply-To: <3F26A503.3050104@trade-india.com>
  References: <200307291114.29567.mallah@trade-india.com> <21657.1059487755@sss.pgh.pa.us> <3F26A503.3050104@trade-india.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox