Обсуждение: Update is more affected( taking more time) than Select ( if Auto vacuum is not running)
Update is more affected( taking more time) than Select ( if Auto vacuum is not running)
От
Hemant Pandey
Дата:
Hi All, I have an ODBC application( using postgres database) which has three different operations. Each operation is having combination of SELECT and UPDATE. For example: Operation A: 6 Fetch + 1 Update Operation B: 9 Fetch Operation C: 5 Fetch + 3 Update ( Tables has 140 records) I have run these operations while Auto Vacumm is running and observed the time taken in thse operations. I found that Operation C is taking highest time and A is the lowest. So i inferrred that, UPDATE takes more time. Now i run these operations again, without running Auto Vacuum. I observed that, time taken for operation A & B is almost same but time for Operation C is increasing. I am not able to analyze, why only for operation C, time is increasing?? Does auto vacuum affects more on UPDATE. Please help me to understand these things. Thanks in advance. Hemant *********************** FSS-Unclassified *********************** "DISCLAIMER: This message is proprietary to Flextronics Software Systems Limited (FSS) and is intended solely for the use of the individual to whom it is addressed. It may contain privileged or confidential information and should not be circulated or used for any purpose other than for what it is intended. If you have received this message in error, please notify the originator immediately. If you are not the intended recipient, you are notified that you are strictly prohibited from using, copying, altering, or disclosing the contents of this message. FSS accepts no responsibility for loss or damage arising from the use of the information transmitted by this email including damage from virus."
Hemant Pandey wrote: > Operation A: 6 Fetch + 1 Update > Operation B: 9 Fetch > Operation C: 5 Fetch + 3 Update ( Tables has 140 records) > > I have run these operations while Auto Vacumm is running and observed the > time taken in thse operations. I found that Operation C is taking highest > time and A is the lowest. > So i inferrred that, UPDATE takes more time. > > Now i run these operations again, without running Auto Vacuum. I observed > that, time taken for operation A & B is almost same but time for Operation > C is increasing. > > I am not able to analyze, why only for operation C, time is increasing?? > Does auto vacuum affects more on UPDATE. Depends on what is happening. Without vacuuming (automatic or manual) a table will tend to have "holes" since an update with MVCC is basically a delete and an insert. Since you say the table has only 140 records, almost any operation will tend to scan rather than use an index. However, if you issue lots of updates you will end up with many "holes" which have to be scanned past. PG won't know they are there because its statistics will be out of date unless you have analysed that table recently. So - everything will start to get slower. So - for a small, rapidly updated table make sure you vacuum a lot (perhaps as often as once a minute). Or, run autovacuum and let it cope. -- Richard Huxton Archonet Ltd