On 8 Září 2011, 14:51, Anibal David Acosta wrote: > Hi! > > > > I have a table not too big but with aprox. 5 millions of rows, this table > must have 300 to 400 select per second. But also must have 10~20 > delete/insert/update per second. > > So, I need to know if the insert/delete/update really affect the select > performance and how to deal with it. Yes, insert/update do affect query performance, because whenever a row is modified a new copy is created. So the table might grow over time, and bigger tables mean more data to read. There are two ways to prevent this: 1) autovacuum - has to be configured properly (watch the table size and number of rows, and if it grows then make it a bit more aggressive) 2) HOT > The table structure is very simple: > > account_id integer (PK) > > service_id integer (PK) > > enabled char(1) > > The index created on this has the same 3 columns. > > Most of time the table has more insert or delete than update, when update > occur the column changed is enabled; So there's one index on all three columns? I'd remove the "enabled" from the index, it's not going to help much I guess and it makes HOT possible (the modified column must not be indexed). Plus there will be one less index (the other two columns are already a PK, so there's a unique index). Tomas -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
