pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Josh Berkus <josh@agliodbs.com>
To: pgsql-sql@postgresql.org
Subject: Performance problems - Indexes and VACUUM
Date: Tue, 16 Oct 2001 20:29:50 -0700
Message-ID: <web-149383@davinci.ethosmedia.com> (raw)

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







view thread (6+ messages)  latest in thread

Message-ID: <web-149383@davinci.ethosmedia.com>
Permalink:  ../web-149383@davinci.ethosmedia.com/
Also on:    postgresql.org/message-id/web-149383@davinci.ethosmedia.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: josh@agliodbs.com
  Subject: Re: Performance problems - Indexes and VACUUM
  In-Reply-To: <web-149383@davinci.ethosmedia.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