X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id D703853A39 for ; Thu, 21 Apr 2005 14:00:25 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 34735-01 for ; Thu, 21 Apr 2005 17:00:20 +0000 (GMT) Received: from web51403.mail.yahoo.com (web51403.mail.yahoo.com [206.190.38.182]) by svr1.postgresql.org (Postfix) with SMTP id 9676C536EF for ; Thu, 21 Apr 2005 14:00:18 -0300 (ADT) Received: (qmail 3000 invoked by uid 60001); 21 Apr 2005 17:00:18 -0000 Comment: DomainKeys? See http://antispam.yahoo.com/domainkeys DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com; b=B0NI9F57RFpvhBbXBR7ELPsVH3UueszpdyRl8ZJv4fPKz5N6I78w2idBhaTKUE91MQU2yQf0YbsVGPPT7nAZ7Li4g2Ax3sKFjgspoOpOHOQ+8i0knoSrdn7FHqQAWHB75egoRntg4hhiBhzutL3p3OGTMiNjFBR6o7ojLtVA+TE= ; Message-ID: <20050421170018.2998.qmail@web51403.mail.yahoo.com> Received: from [192.88.67.254] by web51403.mail.yahoo.com via HTTP; Thu, 21 Apr 2005 10:00:18 PDT Date: Thu, 21 Apr 2005 10:00:18 -0700 (PDT) From: Bill Chandler Subject: Index bloat problem? To: pgsql-perform MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.905 tagged_above=0 required=5 tests=AWL, DNS_FROM_RFC_ABUSE, FROM_ENDS_IN_NUMS X-Spam-Level: X-Archive-Number: 200504/573 X-Sequence-Number: 11970 All, Running PostgreSQL 7.4.2, Solaris. Client is reporting that the size of an index is greater than the number of rows in the table (1.9 million vs. 1.5 million). Index was automatically created from a 'bigserial unique' column. Database contains several tables with exactly the same columns (including 'bigserial unique' column). This is the only table where this index is out of line with the actual # of rows. Queries on this table take 40 seconds to retrieve 2000 rows as opposed to 1-2 seconds on the other tables. We have been running 'VACUUM ANALYZE' very regularly. In fact, our vacuum schedule has probably been overkill. We have been running on a per-table basis after every update (many per day, only inserts occurring) and after every purge (one per day, deleting a day's worth of data). It is theoretically possible that at some time a process was run that deleted all rows in the table followed by a VACUUM FULL. In this case we would have dropped/recreated our own indexes on the table but not the index automatically created for the bigserial column. If that happened, could that cause these symptoms? What about if an out-of-the-ordinary number of rows were deleted (say 75% of rows in the table, as opposed to normal 5%) followed by a 'VACUUM ANALYZE'? Could things get out of whack because of that situation? thanks, Bill __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com