pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
REINDEX question
2+ messages / 2 participants
[nested] [flat]

* REINDEX question
@ 2007-06-22 15:03 Mark Steben <msteben@autorevenue.com>
  2007-06-22 17:24 ` Re: REINDEX question Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 2+ messages in thread

From: Mark Steben @ 2007-06-22 15:03 UTC (permalink / raw)
  To: pgsql-admin

Good morning,

 

We are at Postgres 7.4.5.  I  have encountered inaccuracies 

 after reindexing related to the statistics posted back to the PG_CLASS
table

For instance:

 1.  We have a 'queues' table with 6 indexes.  We are at 110,000 rows in
this table.

      When I reindex, the RELPAGE count decreases as expected but the
RELTUPLE

      Count increases to about 132,000 in all indexes.  I check and there
are still

      Only 110,000 rows on the table.

 2.  We have a table that is quite volatile in its numbers, ranging from 100
to 6 million rows.

     Last week we were at 50,000 rows but PG_CLASS.RELTUPLES showed 6
million for

     The indexes.  I reindexed and, again the RELPAGES count went down but
the RELTUPLES

     Number went down to just 3 million. 

     Again a count after reindex gave the actual row count at 52,000.

 

Does anybody have any insight why this is happening and what I can do in the
short term?

I know we are at a back-leveled version so perhaps the long-term answer is
to upgrade.

 

Thank you,

Mark Steben 

Senior DBA - AutoRevenue

 

Direct dial phone number: 413-327-3045

Please visit our new website at www.autorevenue.com
<http://www.autorevenue.com/; 

"We do all the work....  you make all the money"

 

^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: REINDEX question
  2007-06-22 15:03 REINDEX question Mark Steben <msteben@autorevenue.com>
@ 2007-06-22 17:24 ` Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 2+ messages in thread

From: Tom Lane @ 2007-06-22 17:24 UTC (permalink / raw)
  To: Mark Steben <msteben@autorevenue.com>; +Cc: pgsql-admin

"Mark Steben" <msteben@autorevenue.com> writes:
> We are at Postgres 7.4.5.  I  have encountered inaccuracies 
>  after reindexing related to the statistics posted back to the PG_CLASS
> table

reindex enters the exact number of tuples indexed into
pg_class.reltuples.  If there are more than you expect, that says there
are some dead-but-not-removable tuples in the relation.  Check for old
open transactions that might still be able to see old versions of rows.

VACUUM VERBOSE should report the same number of unremovable rows BTW.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 2+ messages in thread


end of thread, other threads:[~2007-06-22 17:24 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-06-22 15:03 REINDEX question Mark Steben <msteben@autorevenue.com>
2007-06-22 17:24 ` Tom Lane <tgl@sss.pgh.pa.us>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox