X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 31795D1B342 for ; Tue, 13 Jul 2004 13:51:04 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 39091-10 for ; Tue, 13 Jul 2004 16:50:55 +0000 (GMT) Received: from mailer.elma.loc (mail.elma.fr [213.41.14.138]) by svr1.postgresql.org (Postfix) with ESMTP id C7D30D1B23E for ; Tue, 13 Jul 2004 13:50:53 -0300 (ADT) Received: from mailer.elma.loc (localhost [127.0.0.1]) by localhost (Postfix) with ESMTP id 727B0EC0BA for ; Tue, 13 Jul 2004 17:54:10 +0200 (CEST) Received: from zoot.elma.fr (herve.elma.fr [10.0.1.2]) by mailer.elma.loc (Postfix) with ESMTP id 5B64EEC0AA for ; Tue, 13 Jul 2004 17:54:10 +0200 (CEST) From: =?iso-8859-15?q?Herv=E9_Piedvache?= Organization: Elma =?iso-8859-15?q?Ing=E9nierie?= Informatique To: pgsql-performance@postgresql.org Subject: Insert are going slower ... Date: Tue, 13 Jul 2004 18:50:52 +0200 User-Agent: KMail/1.6.2 MIME-Version: 1.0 Content-Disposition: inline Content-Type: text/plain; charset="iso-8859-15" Content-Transfer-Encoding: 8bit Message-Id: <200407131850.52095.herve@elma.fr> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200407/73 X-Sequence-Number: 7464 Hi, I have a database with 10 tables having about 50 000 000 records ... Every day I have to delete about 20 000 records, inserting about the same in one of this table. Then I make some agregations inside the other tables to get some week results, and globals result by users. That mean about 180 000 to 300 000 insert by table each days. The time for the calculation of the request is about 2 / 4 minutes ... I do the request inside a temporary table ... then I do an insert into my_table select * from temp_table. And that's the point, the INSERT take about (depending of the tables) 41 minutes up to 2 hours ... only for 180 to 300 000 INSERTs ... The table have index really usefull ... so please do not tell me to delete some of them ... and I can't drop them before inserting data ... it's really too long to regenerate them ... I'm configured with no flush, I have 8 Gb of RAM, and RAID 5 with SCSI 7200 harddrive ... I'm using Linux Debian, with a PostgreSQL version compiled by myself in 7.4.3. What can I do to get better results ?? (configuration option, and/or hardware update ?) What can I give you to get more important informations to help me ? Regards, -- Hervé Piedvache Elma Ingénierie Informatique 6 rue du Faubourg Saint-Honoré F-75008 - Paris - France Pho. 33-144949901 Fax. 33-144949902