It looks like the indexes are blown.... Which may indicate some corruption in the pg_indexes system catalog table?
Is there a way to check the integrity of pg_indexes for possible page corruption? Nothing is being reported in the postgresql log file that would indicate there is a corruption issue. Thanks, Keaton On 7/8/08 6:24 AM, "Keaton Adams" <[EMAIL PROTECTED]> wrote: Good point. But.... This is a database used to capture logged information, such as success/failure of an operation. Daily tables are rolled up to weekly tables, weekly tables are rolled up to monthly tables. All logged activity are done by inserts, never any updates or deletes, to avoid having to do a vacuum full. So rows are added for seven days to the weekly table (kda_log_info_2008w24) and that's it. Once a new week begins the data is static until enough weeks pass by where the weekly information is summarized at a higher level and stored in a monthly table, then the weekly table is dropped. I'll send out a full EXPLAIN from the original query, which was against the view, so you can see the scope of the issue. Thanks for the reply, Keaton On 7/8/08 4:32 AM, "Dennis Brakhane" <[EMAIL PROTECTED]> wrote: On Tue, Jul 8, 2008 at 12:06 AM, Keaton Adams <[EMAIL PROTECTED]> wrote: > An analyze is run on the tables every day (even several times a day because > they are updated very frequently) and a vacuum analyze is run on the > weekends. I also tried to run an analyze specifically on the customer_id > column and then the product_id column but that didn't help. I'm no expert, so if I'm talking nonsense here, someone please correct me. >From what I read on this list I believe you need to run VACUUM frequently if there are many updates, at least daily, but it could also be needed every n minutes in extreme cases, an ANALYSE won't cut it. (UPDATE leaves dead rows in the database, which must be filtered out by count(*), vacuum gets rid of them) Try a VACUUM FULL (at a time when there isn't much load on the server), if this solves your problem, you need to decrease your VACUUM interval.
