From: Tom Lane <tgl@sss.pgh.pa.us>
To: Kyle <kyle@actarg.com>
Cc: pgsql-sql@postgresql.org
Cc: JanWieck@Yahoo.com
Subject: Re: Strange slow behavior in backend
Date: Wed, 13 Dec 2000 21:28:35 -0500
Message-ID: <18792.976760915@sss.pgh.pa.us> (raw)
In-Reply-To: <3A3795BC.44C91514@actarg.com>
References: <3A3795BC.44C91514@actarg.com>
Kyle <kyle@actarg.com> writes:
> This worked great until I put a real big file in (about 5M). Then, when
> I tried to fetch the file, it seemed really slow (about 60 seconds). I
> tried reassembling the file in the frontend instead and my time dropped
> to about 6 seconds using this TCL fragment (mpg::qlist is an interface
> to pg_exec that returns a list of tuples):
> The only difference I can identify is whether the re-assembly TCL code
> is running as a procedural language (backend) or in the frontend.
> Anyone have any idea why the difference is so dramatic?
I happened to have handy a 7.1 backend compiled for profiling, so I
looked into this a little. I confirm that this seems unreasonably slow.
As near as I can tell, 98% of the backend runtime is being spent in
strlen() and strcpy() invoked from Tcl_SetResult invoked from Tcl_Eval
invoked from the per-result-tuple loop in pltcl_SPI_exec. Apparently,
all this is happening because Tcl_Eval thinks it needs to make the
result of the append command available for its caller. I changed the
inner loop to
spi_exec -array d "select data from pg_largeobject where
loid = $1 order by pageno" {
append odata $d(data);
set z z
}
and voila, the runtime dropped to something reasonable.
So, yes, it would seem that some care in the inner loop of
pltcl_SPI_exec would help a lot. It'd be worth if'defing the Tcl_Eval
call there to use a new-style call when using Tcl 8. (This could also
avoid repetitive parsing of the loop body.) Might want to think about
the same for the Tcl function as a whole, too.
I was also distressed to notice that pltcl_set_tuple_values does a LOT
of repetitive work --- it should be fixed so that the syscache and
function lookups are done only once, not once per tuple.
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-sql@postgresql.org
Cc: tgl@sss.pgh.pa.us, kyle@actarg.com, JanWieck@Yahoo.com
Subject: Re: Strange slow behavior in backend
In-Reply-To: <18792.976760915@sss.pgh.pa.us>
References: <3A3795BC.44C91514@actarg.com> <18792.976760915@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