Re: Performance problems - Indexes and VACUUM

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
>