Received: from thor.tht.net (thor.tht.net [209.47.145.4]) by mail.postgresql.org (8.11.1/8.11.1) with ESMTP id eBEFEb004422 for ; Thu, 14 Dec 2000 10:14:37 -0500 (EST) (envelope-from janwieck@yahoo.com) Received: from jupiter.jw.home (IDENT:root@A58bc.pppool.de [213.6.88.188]) by thor.tht.net (8.9.3/8.9.3) with ESMTP id KAA93095 for ; Thu, 14 Dec 2000 10:36:42 GMT (envelope-from janwieck@Yahoo.com) Received: (from janwieck@localhost) by jupiter.jw.home (8.9.3/8.9.3) id IAA04158; Thu, 14 Dec 2000 08:12:45 -0500 From: Jan Wieck Message-Id: <200012141312.IAA04158@jupiter.jw.home> Subject: Re: Strange slow behavior in backend In-Reply-To: <18792.976760915@sss.pgh.pa.us> from Tom Lane at "Dec 13, 2000 09:28:35 pm" To: Tom Lane Date: Thu, 14 Dec 2000 08:12:45 -0500 (EST) CC: Kyle , pgsql-sql@postgresql.org, JanWieck@yahoo.com X-Mailer: ELM [version 2.4ME+ PL68 (25)] MIME-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit X-Archive-Number: 200012/144 X-Sequence-Number: 478 Tom Lane wrote: > Kyle 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. Hmmm - seems worse than I thought. Again, let's wait with all that until we can overhaul it including the tupleset return mechanism and simply loose pre-8.0 compatibility. Jan -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com #