Skip site navigation (1) Skip section navigation (2)

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

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Rajesh Kumar Mallah <mallah(at)trade-india(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Why performance improvement on converting subselect to a function ?
Date: 2003-07-29 14:09:15
Message-ID: 21657.1059487755@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-performance
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

In response to

Responses

pgsql-performance by date

Next:From: Vivek KheraDate: 2003-07-29 15:14:54
Subject: Re: Tuning PostgreSQL
Previous:From: Shridhar DaithankarDate: 2003-07-29 12:31:36
Subject: Re: Autovacuum

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group