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
