Re: Query running slow but was running fine before

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" <kadams(at)mxlogic(dot)com> 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" <brakhane+psql(at)googlemail(dot)com> wrote:

On Tue, Jul 8, 2008 at 12:06 AM, Keaton Adams <kadams(at)mxlogic(dot)com> 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.