Thread: pg_class.reltuples not reset by VACUUM?
I thought that VACUUM ANALYZE would always restore some sense of reality
to the internal statistics for a table. However ...
# select count(*) from job_queue;
count
-------
834
(1 row)
# select reltuples from pg_class where relname = 'job_queue';
reltuples
-----------
206277
(1 row)
# vacuum analyze job_queue;
# select reltuples from pg_class where relname = 'job_queue';
reltuples
-----------
2062xx (slightly larger number)
(1 row)
This is of concern to me mostly because:
# explain select * from job_queue;
NOTICE: QUERY PLAN:
Seq Scan on job_queue (cost=0.00..5989.77 rows=206277 width=95)
Obviously, the optimizer thinks that the table has 200k rows in it!
--
Jeff Boes vox 616.226.9550 ext 24
Database Engineer fax 616.349.9076
Nexcerpt, Inc. http://www.nexcerpt.com
...Nexcerpt... Extend your Expertise
Jeff Boes <jboes@nexcerpt.com> writes:
> I thought that VACUUM ANALYZE would always restore some sense of reality
> to the internal statistics for a table. However ...
Could we see the output of VACUUM VERBOSE for that table?
I suspect you have lots of dead-but-not-yet-reclaimable tuples in the
table, presumably because there is some very old transaction lurking
in the background.
regards, tom lane
On Tue, 2003-04-08 at 13:52, Tom Lane wrote:
> Jeff Boes <jboes@nexcerpt.com> writes:
> > I thought that VACUUM ANALYZE would always restore some sense of reality
> > to the internal statistics for a table. However ...
>
> Could we see the output of VACUUM VERBOSE for that table?
No, not since I've dropped and recreated it...
>
> I suspect you have lots of dead-but-not-yet-reclaimable tuples in the
> table, presumably because there is some very old transaction lurking
> in the background.
>
Well, here's the present state of the table, 24 hours later:
explain select count(*) from job_queue;
NOTICE: QUERY PLAN:
Aggregate (cost=11248.76..11248.76 rows=1 width=0)
-> Seq Scan on job_queue (cost=0.00..10357.81 rows=356381 width=0)
EXPLAIN
# select count(*) from job_queue;
count
-------
2369
(1 row)
# vacuum analyze verbose job_queue;
NOTICE: --Relation job_queue--
NOTICE: Pages 6831: Changed 2, Empty 0; Tup 358441: Vac 0, Keep 356048,
UnUsed 5.
Total CPU 0.00s/0.15u sec elapsed 0.16 sec.
NOTICE: --Relation pg_toast_292377168--
NOTICE: Pages 0: Changed 0, Empty 0; Tup 0: Vac 0, Keep 0, UnUsed 0.
Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
NOTICE: Analyzing job_queue
VACUUM
# explain select count(*) from job_queue;
NOTICE: QUERY PLAN:
Aggregate (cost=11311.51..11311.51 rows=1 width=0)
-> Seq Scan on job_queue (cost=0.00..10415.41 rows=358441 width=0)
EXPLAIN
# analyze verbose job_queue;
NOTICE: Analyzing job_queue
ANALYZE
xifos:~ # explain select count(*) from job_queue;
NOTICE: QUERY PLAN:
Aggregate (cost=6861.41..6861.41 rows=1 width=0)
-> Seq Scan on job_queue (cost=0.00..6855.33 rows=2433 width=0)
EXPLAIN
--
Jeff Boes vox 269.226.9550 ext 24
Database Engineer fax 269.349.9076
Nexcerpt, Inc. http://www.nexcerpt.com
...Nexcerpt... Extend your Expertise
Jeff Boes <jboes@nexcerpt.com> writes:
> Well, here's the present state of the table, 24 hours later:
> # select count(*) from job_queue;
> count
> -------
> 2369
> (1 row)
> # vacuum analyze verbose job_queue;
> NOTICE: --Relation job_queue--
> NOTICE: Pages 6831: Changed 2, Empty 0; Tup 358441: Vac 0, Keep 356048,
> UnUsed 5.
> Total CPU 0.00s/0.15u sec elapsed 0.16 sec.
Yup, you definitely have a big problem with dead-but-unreclaimed tuples.
Look for some client that's holding an open transaction for long
periods.
regards, tom lane
On Tue, 2003-04-08 at 15:10, Tom Lane wrote: > Yup, you definitely have a big problem with dead-but-unreclaimed tuples. > Look for some client that's holding an open transaction for long > periods. It's even weirder than that: note in the following how VACUUM sets the tuple count high, and ANALYZE sets it low. This appears to happen even if there are no transactions open. # explain select count(*) from job_queue; NOTICE: QUERY PLAN: Aggregate (cost=7064.26..7064.26 rows=1 width=0) -> Seq Scan on job_queue (cost=0.00..7056.81 rows=2981 width=0) EXPLAIN # vacuum job_queue; VACUUM # explain select count(*) from job_queue; NOTICE: QUERY PLAN: Aggregate (cost=11690.88..11690.88 rows=1 width=0) -> Seq Scan on job_queue (cost=0.00..10764.70 rows=370470 width=0) EXPLAIN # analyze job_queue; ANALYZE # explain select count(*) from job_queue; NOTICE: QUERY PLAN: Aggregate (cost=7097.34..7097.34 rows=1 width=0) -> Seq Scan on job_queue (cost=0.00..7089.87 rows=2987 width=0) EXPLAIN -- Jeff Boes vox 269.226.9550 ext 24 Database Engineer fax 269.349.9076 Nexcerpt, Inc. http://www.nexcerpt.com ...Nexcerpt... Extend your Expertise
Jeff Boes <jboes@nexcerpt.com> writes:
> On Tue, 2003-04-08 at 15:10, Tom Lane wrote:
>> Yup, you definitely have a big problem with dead-but-unreclaimed tuples.
>> Look for some client that's holding an open transaction for long
>> periods.
> It's even weirder than that: note in the following how VACUUM sets the
> tuple count high, and ANALYZE sets it low.
That doesn't surprise me, given what they count.
> This appears to happen even if there are no transactions open.
You have open transactions. Go find 'em...
regards, tom lane
On Tue, 2003-04-08 at 15:19, Tom Lane wrote: > > This appears to happen even if there are no transactions open. > > You have open transactions. Go find 'em... > Hmm. Maybe, maybe not. After I shut down everything, I got "Parent tuple not found" errors. I bounced the database, and it cleared up. -- Jeff Boes vox 269.226.9550 ext 24 Database Engineer fax 269.349.9076 Nexcerpt, Inc. http://www.nexcerpt.com ...Nexcerpt... Extend your Expertise
Out of curiosity, are you doing non-superuser vacuums on 7.2.3 or older system? Robert Treat On Tue, 2003-04-08 at 16:30, Jeff Boes wrote: > On Tue, 2003-04-08 at 15:19, Tom Lane wrote: > > > > This appears to happen even if there are no transactions open. > > > > You have open transactions. Go find 'em... > > > > Hmm. Maybe, maybe not. After I shut down everything, I got > > "Parent tuple not found" > > errors. I bounced the database, and it cleared up. > > -- > Jeff Boes vox 269.226.9550 ext 24 > Database Engineer fax 269.349.9076 > Nexcerpt, Inc. http://www.nexcerpt.com > ...Nexcerpt... Extend your Expertise > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster
On Thu, 2003-04-10 at 14:51, Robert Treat wrote: > Out of curiosity, are you doing non-superuser vacuums on 7.2.3 or older > system? > These are VACUUMs as the user "nexcerpt". Database version is 7.2.3. -- Jeff Boes vox 269.226.9550 ext 24 Database Engineer fax 269.349.9076 Nexcerpt, Inc. http://www.nexcerpt.com ...Nexcerpt... Extend your Expertise
On Thu, 2003-04-10 at 14:51, Robert Treat wrote: > Out of curiosity, are you doing non-superuser vacuums on 7.2.3 or older > system? > And I probably didn't answer your question. The username used to perform all VACUUMs is marked "usesuper = t" in pg_shadow. I interpret that as a superuser. Am I right? -- Jeff Boes vox 269.226.9550 ext 24 Database Engineer fax 269.349.9076 Nexcerpt, Inc. http://www.nexcerpt.com ...Nexcerpt... Extend your Expertise
On Thu, 2003-04-10 at 16:42, Jeff Boes wrote: > On Thu, 2003-04-10 at 14:51, Robert Treat wrote: > > Out of curiosity, are you doing non-superuser vacuums on 7.2.3 or older > > system? > > > > And I probably didn't answer your question. The username used to > perform all VACUUMs is marked "usesuper = t" in pg_shadow. I interpret > that as a superuser. Am I right? > usesuper does indicate that the user is a super user. The reason I asked was your problem sounded suspiciously like the "No one parent tuple was found" error that was fixed in 7.2.4. I *thought* that those cases we're the result of vacuuming as non-superuser, but you may want to search the archives if it pops up again, there might be other cases that cause it. Oh, I guess I should make the obligatory "you might want to upgrade" suggestion as well :-) Robert Treat