Josh:
Sorry for the reply to the existing subject!
The newly added indexes have made all other queries much slower except
the uploading ops.
As a result, all the CPU's are running crazy but not much is getting
finished and our Application
Server waits for certain time and then times out. Customers thought the
system hung.
My guess is that all the queries that involves the columns that are
being indexed need to
be rewritten to use the newly created indexes to avoid the performance
issues. The reason
is that REINDEX does not help either. Does it make sense?
Thanks!
Qing
On Sep 16, 2004, at 2:05 PM, Josh Berkus wrote:
> Qing,
>
> Please don't start a new question by replying to someone else's
> e-mail. It
> confuses people and makes it unlikely for you to get help.
>
>> My suspicion is that since now a few indexes are added, every ops are
>> run by PostgreSQL with the indexes being used when calculating cost.
>> This leads to the downgrade of performance.
>
> That seems rather unlikely to me. Unless you've *really* complex
> queries
> and some unusual settings, you can't swamp the CPU through query
> planning.
>
> On the other hand, your mention of REINDEX indicates that the table is
> being
> updated very frequently. If that's the case, then the solution is
> probably
> for you to cut back on the number of indexes.
>
> --
> --Josh
>
> Josh Berkus
> Aglio Database Solutions
> San Francisco
>