From: Tom Lane <tgl@sss.pgh.pa.us>
To: Rajesh Kumar Mallah <mallah@trade-india.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Why performance improvement on converting subselect to a function ?
Date: Tue, 29 Jul 2003 10:09:15 -0400
Message-ID: <21657.1059487755@sss.pgh.pa.us> (raw)
In-Reply-To: <200307291114.29567.mallah@trade-india.com>
References: <200307291114.29567.mallah@trade-india.com>
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
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: tgl@sss.pgh.pa.us, mallah@trade-india.com
Subject: Re: Why performance improvement on converting subselect to a function ?
In-Reply-To: <21657.1059487755@sss.pgh.pa.us>
References: <200307291114.29567.mallah@trade-india.com> <21657.1059487755@sss.pgh.pa.us>
* 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