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

On Wed, Mar 28, 2007 at 11:07:54AM -0400, Tom Lane wrote:
> Stephen Harris <lists(at)spuddy(dot)org> 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