Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 2A9C765010B for ; Sat, 23 Aug 2008 20:50:03 -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 17769-02 for ; Sat, 23 Aug 2008 20:49:55 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from rv-out-0506.google.com (rv-out-0506.google.com [209.85.198.226]) by postgresql.org (Postfix) with ESMTP id B00F665007C for ; Sat, 23 Aug 2008 20:49:59 -0300 (ADT) Received: by rv-out-0506.google.com with SMTP id b25so1745192rvf.43 for ; Sat, 23 Aug 2008 16:49:58 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from:to :subject:cc:in-reply-to:mime-version:content-type :content-transfer-encoding:content-disposition:references; bh=VrCQZVwOta10oObaswDYvUtmsZOPyJnojeMrbthcMTA=; b=SPkkQwfaHz/aZoN3m4Ry5Wq/X5VW61ElCrJnEZs/sUIaD1tJFLx0T36QAyg2i896gY Vpx23Cw9Xj13TwgaWK3EOmDJO+J2/VNc5nb4uALbNZhXkNGDXq9W75jYxkKc88LgdMzS I1vQ81ZnNAT6gSGbTdo7RRH8kjSYE2mPbxJzs= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:to:subject:cc:in-reply-to:mime-version :content-type:content-transfer-encoding:content-disposition :references; b=ukByuMlxOdqKfsQaGeek6vtKikY8L2/uzi221mK1so81TAIUYHsi4NegdN+oTrs5NX bI41Re0P3wQyyAgMt9NxLgdb+q1s7jlelQSWPMCjmUglmb+EgVSpsYwo5soFGxR9Ftkz 9IhINbobzPgQ/LbVko1kdAw3HyZQEH5BPgncA= Received: by 10.140.201.8 with SMTP id y8mr1339435rvf.148.1219535398151; Sat, 23 Aug 2008 16:49:58 -0700 (PDT) Received: by 10.141.197.1 with HTTP; Sat, 23 Aug 2008 16:49:58 -0700 (PDT) Message-ID: Date: Sat, 23 Aug 2008 17:49:58 -0600 From: "Scott Marlowe" To: tls.wydd@free.fr Subject: Re: Large number of tables slow insert Cc: DiezelMax , pgsql-performance@postgresql.org In-Reply-To: <1219520134.48b0668645ce4@imp.free.fr> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline References: <1775c5ea0808221841u746862e3h1b721ea82fdb7794@mail.gmail.com> <48AFE6BB.6040400@nts.biz.ua> <1219520134.48b0668645ce4@imp.free.fr> 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/295 X-Sequence-Number: 31176 On Sat, Aug 23, 2008 at 1:35 PM, wrote: > Actually, I've got another test system with only few sensors (thus few tables) > and it's working well (<10ms insert) with all the indexes. > I know it's slowing down my performance but I need them to interogate the big > tables (each one can reach millions rows with time) really fast. It's quite likely that on the smaller system the indexes all fit into memory and only require writes, while on the bigger system they are too large and have to be read from disk first, then written out. A useful solution is to remove most of the indexes on the main server, and set up a slony slave with the extra indexes on it to handle the reporting queries.