Received: from localhost (maia-4.hub.org [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 771A59FC408 for ; Fri, 22 Jun 2007 12:11:56 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 34692-03-7 for ; Fri, 22 Jun 2007 12:11:46 -0300 (ADT) X-Greylist: delayed 00:06:38.925934 by SQLgrey-1.7.5 Received: from mail.autorevenue.com (ip-208.178.167.106.gblx.net [208.178.167.106]) by postgresql.org (Postfix) with ESMTP id 55E0C9FC3FE for ; Fri, 22 Jun 2007 12:11:46 -0300 (ADT) Received: (qmail 9548 invoked from network); 22 Jun 2007 11:07:05 -0400 Received: from unknown (HELO D1RPQZB1) (msteben@192.168.1.181) by mail.autorevenue.com with SMTP; 22 Jun 2007 11:07:05 -0400 From: "Mark Steben" To: Subject: REINDEX question Date: Fri, 22 Jun 2007 11:03:43 -0400 Message-ID: <015901c7b4de$86bb9c10$b501a8c0@D1RPQZB1> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_015A_01C7B4BC.FFA9FC10" X-Mailer: Microsoft Office Outlook 11 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.2929 Thread-Index: Ace03oaeDyKuGkGkSViZvvFJBPYZEg== X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.137 tagged_above=0 required=5 tests=BAYES_50=0.001, FORGED_RCVD_HELO=0.135, HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 200706/247 X-Sequence-Number: 25915 This is a multi-part message in MIME format. ------=_NextPart_000_015A_01C7B4BC.FFA9FC10 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit 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 "We do all the work.... you make all the money" ------=_NextPart_000_015A_01C7B4BC.FFA9FC10 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

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

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

 

------=_NextPart_000_015A_01C7B4BC.FFA9FC10--