Re: Performance problems - Indexes and VACUUM

Tom,

> Kinda hard to believe; even if the old indexes were still around,
> they
> wouldn't be considered to apply to the new table. I think the
> problem
> is something else. Can you provide a reproducible example of what
> you're seeing?

Wish I could; it only seems to happen on the production machine ... that
is, you need a heavy load of daily use to make it happen. But, to
reduce the issue to its essentials:

1. Create a "pointer table" as I discussed in the last e-mail.
2. Run a bunch of queries that will store several thousand records in
this pointer table, referencing the PK's of more than one data table.
3. In a function, drop the table and re-create it and its indexes.
4. In the same function, reset the sequence you use to identify each
unique user-query to 1.
5. Performing some queries using the pointer tables, some of the
references will mysteriously point to the wrong rows in the data tables.
Some will work correctly.

This is on 7.1.2 (SuSE 7.2, ReiserFS, PG built from source). Explicitly
dropping the indexes before dropping the tables seems to have solved the
problem. My guess, without understanding the guts of the thing at all,
is that the transactional nature of the drop and re-create causes the
indexes not to be fully cleared before they are re-built. Maybe it's
even a reaction to the journaling file system.

BTW, any issues with PostgreSQL and DMA disk access?

> > runs in about 2 seconds right after a VACUUM.
> > Unfortunately, 6 hours after a VACUUM, the query bogs down.
>
> What has been changing in the meantime?

Lots of data edits and adds. This particularly seems to happen on days
where the users are changing dozens to hundreds of records that affect
one of the custom aggregate subqueries.

I'm not surprised things slow down in these circumstances, it's just the
amount of slowdown -- 25 to 1 over a mere 6 hours -- that surprised me.
But the more we talk about this, the more I think I should stop bugging
you and let you finish 7.2 so I can just do background VACUUMing.

-Josh Berkus

______AGLIO DATABASE SOLUTIONS___________________________
Josh Berkus
Complete information technology josh(at)agliodbs(dot)com
and data management solutions (415) 565-7293
for law firms, small businesses fax 621-2533
and non-profit organizations. San Francisco