On 28 Aug 2001, Doug McNaught wrote:
> > Maybe stale indexes? Aborted vacuums? What on earth would cause that?
>
> VACUUM doesn't currently vacuum indexes. Yes, it's a serious wart. :(
Ah, now that makes sense. It would also explain why our daily inserts
of many thousands of rows on a fairly regular basis would slowly bloat
the db. It would also explain why the old system, which didn't use
indexes at all, didn't have this problem. It would also explain why
the query optimizer picks crap plans, since the indexes are completely
innaccurate.
Hmm. That's more than a wart, that's nearly a show-stopping bug.
> I suggest drop/recreate the indexes at intervals. Or try REINDEX,
> which may work better.
Reindex is really our only option. The database schema is complex enough
that dropping and recreating the indexes is dangerous (esp. primary keys)
and we also want to keep user databases from doing this - and we don't
know the details of those DB's.
Unfortunately, reindex can only be run while the DB is down. ::sigh::
So, looks like a cron job to run at 2am.
# --- Pseudocode --- #
Get list of DB's.
Take backend down.
For each DB
REINDEX DATABASE DB
done
Put backend back up.
print "Damn Vacuum."
# --- End Pseudocode --- #
Ewwwww....
--
+-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-+
| Shaun M. Thomas INN Database Programmer |
| Phone: (309) 743-0812 Fax : (309) 743-0830 |
| Email: [EMAIL PROTECTED] AIM : trifthen |
| Web : hamster.lee.net |
| |
| "Most of our lives are about proving something, either to |
| ourselves or to someone else." |
| -- Anonymous |
+-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-+
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
