Search Postgresql Archives

Re: Slow sequential scans on one DB but not another; fragmentation?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On Wed, Mar 28, 2007 at 11:07:54AM -0400, Tom Lane wrote:
> Stephen Harris <lists@xxxxxxxxxx> writes:
> > I have one specific table ("sweep_users") that has 900,000 rows in prod
> > and 630,000 on the standby.  On the standby a "select count(*) from
> > sweep_users" takes a couple of seconds.  On production it takes... 240
> > seconds!
> 
> Lots of dead rows/free space perhaps?  What does VACUUM VERBOSE have to
> say about this table on each machine?

I'm vacuuming every night after the inserts are done.

"vacuum analyze verbose" says (from the overnight log) on production:

INFO:  vacuuming "ibusassets.sweep_users"
INFO:  index "sweep_users_host_id_idx" now contains 972662 row versions in 43147 pages
DETAIL:  835831 index row versions were removed.
37954 index pages have been deleted, 20000 are currently reusable.
CPU 3.76s/10.29u sec elapsed 244.73 sec.
INFO:  "sweep_users": removed 835831 row versions in 21579 pages
DETAIL:  CPU 6.77s/6.64u sec elapsed 607.08 sec.
INFO:  "sweep_users": found 835831 removable, 972662 nonremovable row versions in 2890304 pages
DETAIL:  0 dead row versions cannot be removed yet.
There were 112212932 unused item pointers.
0 pages are entirely empty.
CPU 116.15s/48.07u sec elapsed 1145.11 sec.
INFO:  analyzing "ibusassets.sweep_users"
INFO:  "sweep_users": scanned 3000 of 2890304 pages, containing 1236 live rows and 0 dead rows; 1236 rows in sample, 1190805 estimated total rows

-- 

rgds
Stephen


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux