Who is this? ----- Original Message ----- From: Josh Berkus <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> 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 [EMAIL PROTECTED] > 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 > ---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives? http://archives.postgresql.org
