pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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
>




view thread (6+ messages)  latest in thread

Message-ID: <24b801c156d7$b7e12eb0$37140a0a@exim.com>
Permalink:  ../24b801c156d7$b7e12eb0$37140a0a@exim.com/
Also on:    postgresql.org/message-id/24b801c156d7$b7e12eb0$37140a0a@exim.com

 · 

reply

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