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?
On Sep 16, 2004, at 2:05 PM, Josh Berkus wrote:
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 Berkus Aglio Database Solutions San Francisco
---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster