Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id A14C664FCFC for ; Thu, 7 Aug 2008 11:13:00 -0300 (ADT) Received: from postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 45997-02 for ; Thu, 7 Aug 2008 11:12:51 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from pih-relay06.plus.net (pih-relay06.plus.net [212.159.14.133]) by postgresql.org (Postfix) with ESMTP id 68A4D64FCF6 for ; Thu, 7 Aug 2008 11:12:57 -0300 (ADT) Received: from [84.51.143.99] (helo=server3.office.archonet.com) by pih-relay06.plus.net with esmtp (Exim) id 1KR6EX-0007fu-QH; Thu, 07 Aug 2008 15:12:54 +0100 Received: from [192.168.1.36] (dell36.office.archonet.com [192.168.1.36]) by server3.office.archonet.com (Postfix) with ESMTP id 8E859274065; Thu, 7 Aug 2008 15:12:53 +0100 (BST) Message-ID: <489B02E6.4010701@archonet.com> Date: Thu, 07 Aug 2008 15:12:54 +0100 From: Richard Huxton User-Agent: Thunderbird 2.0.0.16 (X11/20080707) MIME-Version: 1.0 To: dforums CC: Merlin Moncure , pgsql-performance@postgresql.org Subject: Re: Plz Heeeelp! performance settings References: <35FABCF85D99464FB00BC5123DC2A70A051D0FB8@s228130hz1ew09.apptix-01.savvis.net> <276541.4189.qm@web25805.mail.ukl.yahoo.com> <35FABCF85D99464FB00BC5123DC2A70A051D11E1@s228130hz1ew09.apptix-01.savvis.net> <48908988.7020204@emolecules.com> <1217436578.6288.44.camel@localhost> <1240.1217440309@sss.pgh.pa.us> <1217441061.6288.47.camel@localhost> <489A21C2.8060801@vieonet.com> <489AA5B5.9080109@vieonet.com> <489AB375.3090901@archonet.com> <489ABC8F.4030007@vieonet.com> <489AD118.5010902@archonet.com> <489AF8E8.6070503@vieonet.com> In-Reply-To: <489AF8E8.6070503@vieonet.com> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Plusnet-Relay: 9f3c6af41efc8132c92c44267e509ea5 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none X-Spam-Level: X-Archive-Number: 200808/40 X-Sequence-Number: 30921 dforums wrote: > The performance problem is really only on the insertion and even more on > the treatment for the aggregation. > > To treat the 3000 entrances and to insert, or update the tables it needs > 10 minutes. > > As I told you I inject 14000 query every 2 minutes, and it needs 10 > minutes to treat 3000 of those query. Sorry - I still don't understand. What is this "treatment" you are doing? > > > > OK. I assume you're happy with the plans you are getting on these > > queries, since you've not provided any information about them. > > The plan seems ok as it use index as well. > here is the plan : > > explain analyse SELECT "insertUpdateTracks"(137,2605, 852, ('2008-08-06 > 19:28:54'::text)::date,3,'dailydisplay',2,NULL); > INFO: method 1 > QUERY PLAN > ------------------------------------------------------------------------------------ > > Result (cost=0.00..0.01 rows=1 width=0) (actual time=1.151..1.151 > rows=1 loops=1) > Total runtime: 1.160 ms There's nothing to do with an index here - this is a function call. > Has you can see the runtime processs for an update in this table. > > multiplying this per 10000, it is too long. So - are you calling this function 14000 times to inject your data? You're doing this in one transaction, yes? -- Richard Huxton Archonet Ltd