Re: [PERFORM] BUG #1697: Select getting slower on continously updating data

[Bahadur Singh]

--- Bruno Wolff III [EMAIL PROTECTED] wrote:

 This does not belong on the pgsql-bugs list. The
 pgsql-novice or
 pgsql-performance lists seem more appropiate. I have
 set followups
 to the pgsql-novice list.
 
 On Thu, Jun 02, 2005 at 12:05:00 +0100,
   Bahadur Singh [EMAIL PROTECTED] wrote:
  
  Hello,
  
  I found situtation that, when I am selecting data
 from a table of 200
  records, getting slower as I do continous update
 to the same existing data.
 
 You need to be vacuuming (and possibly analyzing)
 the table more often as the
 updates will leave dead rows in the table which will
 bloat the table size and
 slow down access, particularly sequential scans. If
 the updates modify the
 data value distributions significantly, then you
 will also need to
 reanalyze the table to help the planner make good
 decisions.
 

Many thanks for this tip !
But is this good idea to analyse/vacuuming the
database tables while updates are taking place..
Since, I update continuously say (100,000 ) times or
more the same data set.

This is the result of analyze command.

INFO:  analyzing public.salesarticle
INFO:  salesarticle: scanned 3000 of 20850 pages,
containing 62 live rows and 134938 dead rows; 62 rows
in sample, 431 estimated total rows

Gesamtlaufzeit der Abfrage: 5531 ms.
Total Time Taken : 5531 ms.

Can you suggest me some clever way to so, because I
would prefer to do vaccumming while database is not
loaded with queries/transactions.

Regards
Bahadur




__ 
Discover Yahoo! 
Find restaurants, movies, travel and more fun for the weekend. Check it out! 
http://discover.yahoo.com/weekend.html 


---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])

Re: [PERFORM] BUG #1697: Select getting slower on continously updating data

[Bruno Wolff III]

On Fri, Jun 03, 2005 at 00:09:00 -0700,
  Bahadur Singh [EMAIL PROTECTED] wrote:
 
 Many thanks for this tip !
 But is this good idea to analyse/vacuuming the
 database tables while updates are taking place..
 Since, I update continuously say (100,000 ) times or
 more the same data set.
 
 This is the result of analyze command.
 
 INFO:  analyzing public.salesarticle
 INFO:  salesarticle: scanned 3000 of 20850 pages,
 containing 62 live rows and 134938 dead rows; 62 rows
 in sample, 431 estimated total rows
 
 Gesamtlaufzeit der Abfrage: 5531 ms.
 Total Time Taken : 5531 ms.
 
 Can you suggest me some clever way to so, because I
 would prefer to do vaccumming while database is not
 loaded with queries/transactions.

While that may be a nice preference, under your usage pattern that does
not appear to be a good idea. As long as your disk I/O isn't saturated
you want to be running vacuums a lot more often than you are. (Analyze should
only be needed if the distrution of values is changing constantly. An example
would be timestamps indicating when an update occured.)

---(end of broadcast)---
TIP 8: explain analyze is your friend