Received: from localhost (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id 541D06356DE for ; Fri, 6 Nov 2009 11:39:06 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024) with ESMTP id 07671-05-4 for ; Fri, 6 Nov 2009 15:38:47 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from gw.wicourts.gov (gwmta.wicourts.gov [165.219.244.99]) by mail.postgresql.org (Postfix) with ESMTP id 2AC0263475B for ; Fri, 6 Nov 2009 11:26:36 -0400 (AST) Received: from Courts-MTA by gw.wicourts.gov with Novell_GroupWise; Fri, 06 Nov 2009 09:26:34 -0600 Message-Id: <4AF3EBC3020000250002C329@gw.wicourts.gov> X-Mailer: Novell GroupWise Internet Agent 8.0.0 Date: Fri, 06 Nov 2009 09:26:27 -0600 From: "Kevin Grittner" To: "Grant Masan" Cc: "Jeff Janes" , Subject: Re: Problem with database performance, Debian 4gb ram ? References: <4AEFF447020000250002C19D@gw.wicourts.gov> <4AF019E6020000250002C1D2@gw.wicourts.gov> In-Reply-To: Mime-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit Content-Disposition: inline X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-2.59 tagged_above=-10 required=5 tests=AWL=0.009, BAYES_00=-2.599 X-Spam-Level: X-Archive-Number: 200911/77 X-Sequence-Number: 36245 Please keep the list copied. Grant Masan wrote: > CREATE FUNCTION ... RETURNS SETOF ... > FOR ... IN SELECT ... LOOP > FOR ... IN SELECT ... LOOP > FOR ... IN SELECT ... LOOP > > RETURN NEXT text_output; > > END LOOP; > END LOOP; > END LOOP; I don't have time to work through the logic of all this to try to discern what your goal is; but in my experience, such procedural code can usually be rewritten as a single query. The results are typically orders of magnitude better. > SELECT * FROM info_tool(linest,date,date) > "Function Scan on info_tool (cost=0.00..260.00 rows=1000 width=108) > (actual time=437712.611..437712.629 rows=14 loops=1)" > "Total runtime: 437712.686 ms" To get useful information you need EXPLAIN ANALYZE from statements inside the function, not of the execution of the function. -Kevin