Received: from localhost (maia-1.hub.org [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id 25FA19FA4AE for ; Mon, 4 Jun 2007 15:54:23 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-maia, port 10024) with ESMTP id 68219-01-7 for ; Mon, 4 Jun 2007 15:54:17 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from thomasoandrews.com (thomasoandrews.com [192.220.68.240]) by postgresql.org (Postfix) with ESMTP id 33DDC9FB291 for ; Mon, 4 Jun 2007 15:53:34 -0300 (ADT) Received: (qmail 60696 invoked by uid 17648); 4 Jun 2007 18:46:52 -0000 Received: from unknown (HELO thomas-andrews-computer-2.local) ([24.62.5.51]) (envelope-sender ) by 192.220.68.240 (qmail-ldap-1.03) with SMTP for ; 4 Jun 2007 18:46:52 -0000 Message-ID: <46645E1B.6000808@soliantconsulting.com> Date: Mon, 04 Jun 2007 14:46:51 -0400 From: Thomas Andrews User-Agent: Thunderbird 2.0.0.0 (Macintosh/20070326) MIME-Version: 1.0 To: Mark Lewis CC: pgsql-performance@postgresql.org Subject: Re: Thousands of tables versus on table? References: <1180980943.31471.322.camel@archimedes> In-Reply-To: <1180980943.31471.322.camel@archimedes> Content-Type: multipart/mixed; boundary="------------090108070605040901000904" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.886 tagged_above=0 required=5 tests=AWL=0.750, BAYES_50=0.001, FORGED_RCVD_HELO=0.135 X-Spam-Level: X-Archive-Number: 200706/37 X-Sequence-Number: 25070 This is a multi-part message in MIME format. --------------090108070605040901000904 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Oh, and we vacuum every day. Not sure about REINDEX, but I doubt we have done that. =thomas Mark Lewis wrote: > On Mon, 2007-06-04 at 13:40 -0400, Thomas Andrews wrote: >> I have several thousand clients. Our clients do surveys, and each survey >> has two tables for the client data, >> >> responders >> responses >> >> Frequent inserts into both table. >> >> Right now, we are seeing significant time during inserts to these two >> tables. > > Can you provide some concrete numbers here? Perhaps an EXPLAIN ANALYZE > for the insert, sizes of tables, stuff like that? > >> Some of the indices in tableA and tableB do not index on the client ID >> first. > > What reason do you have to think that this matters? > >> So, we are considering two possible solutions. >> >> (1) Create separate responders and responses tables for each client. >> >> (2) Make sure all indices on responders and responses start with the >> client id (excepting, possibly, the primary keys for these fields) and >> have all normal operation queries always include an id_client. >> >> Right now, for example, given a responder and a survey question, we do a >> query in responses by the id_responder and id_survey. This gives us a >> unique record, but I'm wondering if maintaining the index on >> (id_responder,id_survey) is more costly on inserts than maintaining the >> index (id_client,id_responder,id_survey) given that we also have other >> indices on (id_client,...). >> >> Option (1) makes me very nervous. I don't like the idea of the same sorts >> of data being stored in lots of different tables, in part for long-term >> maintenance reasons. We don't really need cross-client reporting, however. > > What version of PG is this? What is your vacuuming strategy? Have you > tried a REINDEX to see if that helps? > > -- Mark Lewis > --------------090108070605040901000904 Content-Type: text/x-vcard; charset=utf-8; name="tandrews.vcf" Content-Transfer-Encoding: 7bit Content-Disposition: attachment; filename="tandrews.vcf" begin:vcard fn:Thomas Andrews n:Andrews;Thomas org:Soliant Consulting adr;dom:;; 14 N. Peoria Street 2H;Chicago;IL;60607 email;internet:tandrews@soliantconsulting.com title:Application Engineer tel;work:617-868-5580 tel;home:617-868-5580 tel;cell:617-599-8728 x-mozilla-html:FALSE url:http://www.soliantconsulting.com/ version:2.1 end:vcard --------------090108070605040901000904--