Received: from cyclops.soft.net (relay1.soft.net [164.164.128.17]) by postgresql.org (8.11.3/8.11.4) with ESMTP id f9H6pFP28087 for ; Wed, 17 Oct 2001 02:51:18 -0400 (EDT) (envelope-from kusum.l@eximsoft.com) Received: from email.eximsoft.com (gw.eximsoft.com [164.164.87.130]) by cyclops.soft.net (Switch-2.0.1/Switch-2.0.1) with ESMTP id f9HGpR504158; Wed, 17 Oct 2001 11:51:28 -0500 (GMT) Received: from localhost (localhost.eximsoft.com [127.0.0.1]) by email.eximsoft.com (Postfix) with ESMTP id 5406E690F9; Wed, 17 Oct 2001 12:19:13 +0530 (IST) Received: from corp.eximsoft.com (corp.eximsoft.com [10.10.20.155]) by email.eximsoft.com (Postfix) with ESMTP id F2BD8690F1; Wed, 17 Oct 2001 12:19:10 +0530 (IST) Received: from localhost (localhost [127.0.0.1]) by corp.eximsoft.com (Postfix) with ESMTP id 067A239902; Wed, 17 Oct 2001 12:25:02 +0530 (IST) Received: from ewsco030 (unknown [10.10.20.55]) by corp.eximsoft.com (Postfix) with SMTP id C99DE398C5; Wed, 17 Oct 2001 12:25:00 +0530 (IST) Message-ID: <24b801c156d7$b7e12eb0$37140a0a@exim.com> From: "Kusuma" To: "Josh Berkus" , References: Subject: Re: Performance problems - Indexes and VACUUM Date: Wed, 17 Oct 2001 12:18:24 +0530 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 5.00.2314.1300 X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2314.1300 X-Virus-Scanned: by AMaViS snapshot-20010407 X-Archive-Number: 200110/232 X-Sequence-Number: 5187 Who is this? ----- Original Message ----- From: Josh Berkus To: Sent: Wednesday, October 17, 2001 8:59 AM Subject: [SQL] Performance problems - Indexes and VACUUM > Tom, Folks: > > I am having a rather interesting time getting performance out of my > database. I'd really appreciate some feedback from the list on this. > > As you may recall, I've gotten around Postgres' lack of rowset-returning > stored procedures by constructing "pointer tables" which simply hold > lists of primary keys related to the user's current search. This is an > excellent approach for a browser-based application, and I have since > used this idea on other databases, even one that supports stored > procedures. > > However, this means that I clear all of these pointer tables on a > periodic basis (how frequently depends on usage). Just clearing the > records didn't work, because of the Postgres "padded index" problem > where eventually the indexes on these tables becomes full of deleted > rows. Which gives me problem 1: > > 1. INDEXES: I discovered, the hard way, a peculiar problem. If you drop > and re-create a table within the same transaction (in a function, for > example) the indexes do not get dropped completely. Doing this to > several tables, I had the disturbing experience of seeing incorrect rows > in response to some queries. Specifically dropping each of the indexes, > dropping the tables, re-creating the tables, and re-creating the indexes > seems to work. However, this seems to me to indicate a potential > problem with DDL commands within transactions. > > The second problem is giving me severe grief right now: > > 2. I have a very complex view designed for browsing client information. > This view involves 2 other views, and two custom aggregates which are > based on sub-queries (could only do it in Postgres!). The query plan is > as long as this e-mail, but thanks to optimization and good indexing it > runs in about 2 seconds right after a VACUUM. > Unfortunately, 6 hours after a VACUUM, the query bogs down. The query > plan does not seem to have changed much, but somehow what took 50% of > the processor for 2 seconds at 8:30AM flattens the processor for a full > 45 seconds at 3:30 pm. > Once VACUUM can be run in the background, I suppose that this can be > dealt with, but until then does anyone have any suggestions? > > -Josh Berkus > > > > ______AGLIO DATABASE SOLUTIONS___________________________ > Josh Berkus > Complete information technology josh@agliodbs.com > and data management solutions (415) 565-7293 > for law firms, small businesses fax 621-2533 > and non-profit organizations. San Francisco > ---------------------------------------------------------------------------- ---- > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org >