From: Kusuma <kusum.l@eximsoft.com>
To: Josh Berkus <josh@agliodbs.com>
To: pgsql-sql@postgresql.org
Subject: Re: Performance problems - Indexes and VACUUM
Date: Wed, 17 Oct 2001 12:18:24 +0530
Message-ID: <24b801c156d7$b7e12eb0$37140a0a@exim.com> (raw)
References: <web-149383@davinci.ethosmedia.com>
Who is this?
----- Original Message -----
From: Josh Berkus <josh@agliodbs.com>
To: <pgsql-sql@postgresql.org>
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
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: kusum.l@eximsoft.com, josh@agliodbs.com
Subject: Re: Performance problems - Indexes and VACUUM
In-Reply-To: <24b801c156d7$b7e12eb0$37140a0a@exim.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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